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 ?
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)
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 ?