VLOOKUP column number searching. [Solved/Closed]

Report
Posts
8
Registration date
Thursday April 8, 2010
Status
Member
Last seen
April 26, 2010
-
bob2011
Posts
8
Registration date
Thursday April 8, 2010
Status
Member
Last seen
April 26, 2010
-
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.

5 replies

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
755
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)
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5793 users have said thank you to us this month

Posts
8
Registration date
Thursday April 8, 2010
Status
Member
Last seen
April 26, 2010

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
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
755
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)
Posts
8
Registration date
Thursday April 8, 2010
Status
Member
Last seen
April 26, 2010

That has worked ! thank you very much for your help Rizvisa1 ! :-)