Using a lookup to find a value but the Index column Changes

[t0m46]

Hey all

I have a Speadsheet that has a Table in that i do a Vlookup from to return a Value based on the reference cell that i input a value in.

what i want to be able to do is that the colum i want to look up the value in isnt always going to be the same column. is there a way to do a Vlookup but have the column index number able to look through the coloumn headders that i will put in and uses a cloumn based on what the headder is, EG: Name,

Also by using a Vlookup the value you enter has to be in the first column. is there a way that that can change column as well

thanks

Tom

[Andrew-R]

Funnily enough I've just written a solution to exactly this problem.

The lookup is on sheet 1 and the table is on sheet 2.

[t0m46]

Hey,

Thanks that works well

A problem i am having with it though is that the Lookup uses the first column to look for the input value. is there a way for that to column to change as well?? as in the lookup value is in column D and the range to bring back is in column B?

Thanks

Tom

[DonkeyOte]

INDEX/MATCH is more flexible than a VLOOKUP given it can look in any direction and the criteria can be in any column.

For ex.

=INDEX(sheet1!$A:$Z,MATCH(A1,sheet1!$D:$D,0),MATCH(B1,sheet1!$1:$1,0))

In the above the row retrieved is determined by where the criteria value (A1) is found in Column D of sheet1
The column retrieved is determined by where the criteria value (B1) is found in row 1 of sheet1

@Andrew-R, re:

=VLOOKUP(A1,SearchRange,HLOOKUP("Job",HeaderRange,2,FALSE),FALSE)

In much the same way as the above you don't need the HLOOKUP with Column Row identifiers on Sheet2 - a basic MATCH would suffice:

=VLOOKUP(A1,SearchRange,MATCH("Job",Sheet2!$1:$1,FALSE),FALSE)

[Andrew-R]

Thanks - every day's a school-day. I've been writing Excel formula and macros for 15 years, but I'm still learning a load looking around this forum.

[t0m46]

Hey,

Thanks, Thats getting there.

If i didnt know what Coloumn i wanted to look at is there a way to do a match in that match select a range instead of a Column number. EG, D1:D20 instead of 4

Thanks

Tom

[DonkeyOte]

I think it's all demonstrated in post # 4, no ?

[t0m46]

The bit that i am having problems is

MATCH(A1,sheet1!$D:$D,0),

What if the Value i want to Match with is in a different Column, is there a way so that it allows the column to change.

Eg I want to Match on CODE Column, but if one time i put the data in CODE is column C and the next time import the data CODE is column D, Can i do a Match to find out what the column is to then match on?

thanks

Sorrry if i i wasnt explaining myself that well.

[martindwilson]

you need index(match,match) the second match is the column
so say your headers are in row 1 b1to possibly z1 you'd get the column by
match("code",b1:z1,0) or even =MATCH("code",1:1,0)

[t0m46]

Hey

Have a look at my Example, I get that how it works, But CODE is the header, Underneath CODE is a number of CODES, eg 1,2,3.

What i want is that if the CODE column changes, i dont want to have to retype the formula to look in a different column.

this is kind of the formula i want but it doesnt work.

=Index(A:Z,Match("1",Match("CODE",1:1,0),0),Match("NAME",1:1,0))

the Match in the Match lookes for the coloumn heading CODE and selects that column as the range to then lood down for the CODE of 1.

Thanks

[martindwilson]

what cell are you trying to return ?

[t0m46]

B9 is where you enter the CODE and B11 is where the formula is and where it returns the NAME.

so when you enter the code it brings the persons name related to the code.

[DonkeyOte]

If you want to use A9 to determine the column it's pretty important the value actually matches one of the column headers - it doesn't in your sample.

Assuming you correct A9 such that it reads CODE [rather than Code:] per the header then it's simply a case of repeating the same logic previously outlined:

=INDEX(A1:D4,MATCH(B9,INDEX(A1:D4,0,MATCH(A9,A1:D1,0)),0),MATCH("NAME",A1:D1,0))

So if the CODE Column changes position within A:D the above will update accordingly.

It follows that if A11 is to be a variable also then change that such that it matches the headers also - eg A11 = NAME [rather than Name:] at which point:

=INDEX(A1:D4,MATCH(B9,INDEX(A1:D4,0,MATCH(A9,A1:D1,0)),0),MATCH(A11,A1:D1,0))

so if you change A11 from Name to Number you would get a different result.

[t0m46]

Hey,

Thanks thats exactly what i want, got there in the end. lol

Thanks again for all your help. is all really new to me and this forumn gives me alot of guidance.

Tom