VLOOKUP + MATCH on column headers

Leslie Nivison

Hi, I have a reference sheet set up with a list of milestone names/build versions/dates and am trying to use VLOOKUP + MATCH to auto-pull those in a 2nd sheet.

For example - Sheet1 uses =VLOOKUP("ZBR", Milestones1:[2.0]7, MATCH("1.4", [Column Headers], false))

Where "Milestones1:[2.0]7" is a table of milestones/build versions/dates in the referenced sheet. And [Column Headers] would be the column headers in that same sheet, if I could actually find a way to reference them via formula.

I've also tried pasting the header names in the cells underneath, but it doesn't work right seeing that the column values need to be formatted as dates.

The following screenshot is from my reference sheet.

Leslie Nivison

I figured out a solution that scales with more versions. Isn't nearly as clean compared to excel/googlesheets where you can reference the column headers but it works.

Following screenshot is my reference sheet. Added a helper column so that my individual runbook sheets can pull this data via VLOOKUP and 2 criteria (version + milestone).

Mark Cronk

Hi Leslie,

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark

Mark Cronk

Hi @Leslie Nivison ,

You can't use match to find a column name.

Do you have a cell in your 1st sheet with the version number you want to use, or can your create one? If you do, the following will work:

=VLOOKUP("ZBR", Milestones1:[2.0]7, IF(/insert version/=1.4, 1,IF(/insert version/=1.5,2, IF(/insert version/=1.6,3, 4))), false))

Help?

Mark

Leslie Nivison

Hi Mark, thanks for the feedback. I tried to create another row with the version number so I could actually reference that range but I ran into a different issue. Evidently you have to format the entire column the same way, so I could either display the version # or the milestone dates correctly but not both. Is there a way to work around that?

Mark Cronk

Hi Leslie,

You shouldn't mix data type.

How many versions are in your reference table? The solution I proposed will work if you have few enough they can be placed in a nested IF. Your formula hard codes the 1.4 in the match. What tells you to use 1.4? If the structure of your reference table is static then version 1.4 is always column 2. You would use 2 in your vlookup =VLOOKUP("ZBR", Milestones1:[2.0]7, 2, false)

What am I missing?

Mark

Leslie Nivison

Hi Mark, it's a live product so we'll have an ever expanding list of versions.

>Your formula hard codes the 1.4 in the match. What tells you to use 1.4?

Intention is to reference a column in each runbook sheet that lists the targeted version. So that at least should be static. But the intention is to keep expanding the versions and dates listed in the reference sheet.

Leslie Nivison

I figured out a solution that scales with more versions. Isn't nearly as clean compared to excel/googlesheets where you can reference the column headers but it works.

Following screenshot is my reference sheet. Added a helper column so that my individual runbook sheets can pull this data via VLOOKUP and 2 criteria (version + milestone).

Mark Cronk

Hi Leslie,

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark