vLookup: column name instead of number

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.

Sign in to vote!

Find more posts tagged with

Idea Submitted

Comments

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!

Quick Links

Trending ideas

Resource Management Plan Settings in Control Center - add Client and Project Code fields
In the Resource Management Plan Settings in Control Center, there is no way to link the Client and Project Code fields even though they are not Custom fields and part of the first 3 fields you enter when creating a new project. I can't select them in the Custom Resource Management Information Fields because they are not…
WorkApp Analytics
I’d like to see user analytics for WorkApps. I’ve checked the user report available in the Collaborator Pack, but it only shows how many times someone has clicked into the WorkApp each month. I’d like to know: Which resources (dashboards, sheets, forms, etc.) are getting the most traffic? Where are people spending their…
Attachments in Cells
I rely heavily on Smartsheet’s Document Generation. However, this workflow is currently dependent on having photos stored inside cells, since row attachments alone cannot be used directly in document generation.. If Smartsheet allowed attachments to be displayed as thumbnails in cells (as a reference), document generation…