vLookup: column name instead of number

JamieLim

When using the vlookup formula, is it possible to indicate the column name that we want to return as result instead of the column number? Cos sometimes the columns get reordered, and the results returned by column number will not be the intended column. This is especially so when we are referencing another sheet in the vlookup formula.

Genevieve P.

Hey @JamieLim

Instead of using VLOOKUP, I'd recommend using an INDEX( MATCH combination.

The difference here is that you reference the two columns individually (the column to bring information back from, and the column that has the matching value across both sheets).

=INDEX({Column to return}, MATCH([Matching Value]@row, {Matching Value Column}, 0))

You can title your {column references} something specific so you can see in the formula what it is you're referencing.

Since you're identifying them separately, they can be moved around in the source sheet as much as you'd like and the placement won't affect your formula at all.

Here's a Help Article that goes through how to create this type of formula combination: Formula combinations for cross sheet references

Cheers,

Genevieve

Paul Newcome

@Genevieve P. I was just stopping by to suggest an INDEX/MATCH, but I see you already beat me to it. Haha.

Tabitha Hunter

It would be great if sheet ranges and column references in a vlookup formula that looks up a value in a different sheet would dynamically update when a new column is added to the sheet that contains the values. For example: I have 3 columns in sheet A, Name, Address, and Phone. I have a column in sheet B that uses a vlookup formula to grab the phone number based on the same name using a column reference of 3. If I add a column in sheet A between address and phone, such as phone type, my column reference in sheet B is still 3, therefore pulling the wrong data.

Georgie

@Tabitha Hunter,

I've merged your idea with this one since it's very similar - but take a look at Genevieve's comment above, as an INDEX/MATCH formula should resolve the issue!

Thanks,

Georgie

Tabitha Hunter

This worked perfectly! Thank you so much for pointing me in the direction of a solution!