VLOOKUP column number searching. [Solved/Closed]

bob2011
Posts
8
Registration date
Thursday April 8, 2010
Last seen
April 26, 2010
- Apr 8, 2010 at 10:17 AM - Latest reply: bob2011
Posts
8
Registration date
Thursday April 8, 2010
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.

See more 

4 replies

Best answer
rizvisa1
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Apr 8, 2010 at 05:48 PM
3
Thank you
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)

Thank you, rizvisa1 3

Something to say? Add comment

CCM has helped 1621 users this month

bob2011
Posts
8
Registration date
Thursday April 8, 2010
Last seen
April 26, 2010
- Apr 9, 2010 at 04:13 AM
0
Thank you
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
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- 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
Last seen
April 26, 2010
- Apr 9, 2010 at 08:11 AM
0
Thank you
That has worked ! thank you very much for your help Rizvisa1 ! :-)