VLOOKUP column number searching.

Solved/Closed
bob2011 Posts 8 Registration date Thursday April 8, 2010 Status Member Last seen April 26, 2010 - Apr 8, 2010 at 10:17 AM
bob2011 Posts 8 Registration date Thursday April 8, 2010 Status Member Last seen April 26, 2010 - Apr 9, 2010 at 08:11 AM
Dear All,

I'm currently using a VLOOKUP to populate an information table im creating however im still adding columns to the sheet which holds the information which creates the problem in the fourth part of the vlookup formula, the column number, as it will be moving when new columns of information are added.

So my question is, is there any formula, that can be placed within the vlookup, that will return the column number from a column title ? in other words if the column titles are all in row 6 is there a formula that will search for a name in that row and return the column number, which can then be used in the vlookup formula ?

Thanks for your help.

Related:

3 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 8, 2010 at 05:48 PM
You can try like this

This lookup string "Look for me" in the range A:D, and the column number would be the one that has heading "Phone". Important thing to note is that the VLOOKUP column range (in this example A-D) should match exactly the column range used in MATCH (A-D)

=VLOOKUP("Look for me", A:D,MATCH("Phone",A5:D5,0),FALSE)
bob2011 Posts 8 Registration date Thursday April 8, 2010 Status Member Last seen April 26, 2010
Apr 9, 2010 at 04:13 AM
Thank you for yor reply.

I have tried implementing this, see below, but it does not seem to work. excel is not saying there is an error in the formula it just returns an N/A.

=VLOOKUP(D4,Summary!A6:EI148,VLOOKUP("LOOK FOR ME", Summary!A6:EI6,MATCH("ISIN",Summary!A6:EI6,0),FALSE))

D4 is the name on the current sheet I wish the first vlookup to return a value for.
Summary is the sheet name im searching on.
A6:EI148 is where all of my information is contained in summary.
A6:EI6 is the row with the column titles in in summary.

Does the vlookup in the middle of the first vlookup return the number of the column ?
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 9, 2010 at 06:18 AM
A6:EI6 is not the row. I think you had meant to type A6:E6 where the title are.

Let me explain what my example does
=VLOOKUP("Look for me", A:D,MATCH("Phone",A5:D5,0),FALSE)

MATCH("Phone",A5:D5,0), is scanning row 5 between column A-D and looking for value "PHONE". When it finds it, it gives the relative position between A-D. Then the VLOOKUP looks for string "LOOK FOR ME" in the range A:D (A-D columns). and when match is found it uses the column returns by MATCH. SInce both LOOKUP and MACTCH have same columns, it works. I am not sure if you need two look up

I think you meant this

=VLOOKUP(D4,Summary!A6:EI148, MATCH("ISIN",Summary!A6:E6,0),FALSE)
bob2011 Posts 8 Registration date Thursday April 8, 2010 Status Member Last seen April 26, 2010
Apr 9, 2010 at 08:11 AM
That has worked ! thank you very much for your help Rizvisa1 ! :-)