vlookup help

[coltheplumb]

Hi all
ok i have this formula in EF16 =if(CS16="1",vlookup(CP16,$QE$4:$ER$29,2)) ...it is returning FALSE what i am after is for it to return 3...which is the number that is next to what is in cell CP16.
all help appreciated
Colin X

 

[Aladin Akyurek]

Hi all
ok i have this formula in EF16 =if(CS16="1",vlookup(CP16,$QE$4:$ER$29,2)) ...it is returning FALSE what i am after is for it to return 3...which is the number that is next to what is in cell CP16.
all help appreciated
Colin X

Two observations:

a) "1" should probably be just 1 (no quotes);
b) What is the 4th argument of VLOOKUP? Omitting it means 1 (i.e., TRUE). Is that justified, i.e., the intention?

 

[coltheplumb]

Hi again
OK CS16 will either have a 1 in it or will be empty
CP16 will have a number in it ranging from 20 to 28
The vlookup i have has numbers 20 to 28 in column $EQ$4:$EQ$29 and various individual numbers numbers in column $ER$4:$ER$29
and so what i am after is a formula that will look at CS16 and if it has a 1 in it to then look at CP16 to see what number that is then look at the vlookup range $EQ$4:$ER$29 and return the number that is next to it, the way vlookup works... hope that makes sense??
Colin X

 

[coltheplumb]

I appear to have cracked it =if(CS16=1,vlookup(CP16,$EQ$4:$ER$29,2),0)

 

[Aladin Akyurek]

I appear to have cracked it =if(CS16=1,vlookup(CP16,$EQ$4:$ER$29,2),0)

Must Vlookup look like VLOOKUP(CP16,$EQ$4:$ER$29,2,0) or VLOOKUP(CP16,$EQ$4:$ER$29,2,1)?