Excel LOOKUP help?

[Lefty]

I consider myself a fairly competent user of Excel but I'm getting frustrated by what should be a relatively easy function.

I have text strings in cells E9:E20. These are selecetd from a data validation list which has 30 entries.

I want to put a formula into another cell which does a lookup against E9:E20 using 4 of the entries in that data validation list and then return the appropriate integer in column 2 of the lookup table.

So, I was hoping I could build an IF or OR statement into the vlookup, for example:

=vlookup(E1246,E9:F20,2,false)

E1246 is one of the 4 text strings in my data validation list that I want to use as a lookup reference. I want to make the vlookup use 4 references (E1246, 1247, 1248 and 1249)

Is it possible?

I know I could just do 4 seperate vlookups and perhaps nest them with IF and ISNA but it'll be a massive, untidy formula.



Ta for any help.

[OneDs]

Lefty said:

I consider myself a fairly competent user of Excel but I'm getting frustrated by what should be a relatively easy function.

I have text strings in cells E9:E20. These are selecetd from a data validation list which has 30 entries.

I want to put a formula into another cell which does a lookup against E9:E20 using 4 of the entries in that data validation list and then return the appropriate integer in column 2 of the lookup table.

So, I was hoping I could build an IF or OR statement into the vlookup, for example:

=vlookup(E1246,E9:F20,2,false)

E1246 is one of the 4 text strings in my data validation list that I want to use as a lookup reference. I want to make the vlookup use 4 references (E1246, 1247, 1248 and 1249)

Is it possible?

I know I could just do 4 seperate vlookups and perhaps nest them with IF and ISNA but it'll be a massive, untidy formula.



Ta for any help.

Yes of course, I assume one precedes the other or if 3 of the 4 are blank then you pick the not blank one or something along those lines.

i.e. if(e1246=1st in data list,vlookup(1st),if(e1246 = 2nd in data list,vlookup(2nd) etc.....

or

i.e. if(1st<>"",vlookup(1st),if(2nd<>"",vlookup(2nd) etc.....

you may need to use "if(and" or "if(or" if you wanted to test more than one blank at a time and get it in the 6 concurrent ifs limit.

Edited by OneDs on Wednesday 30th March 11:25

[Lefty]

Thanks for that.

To be clear, there are 4 entries in the data validation table (E1246 to E1249) that will be in the cells E9:E20 that I want to use. There will only ever be one of those 4 text strings in the range E9:E20.

Cheers

[OneDs]

Lefty said:

Thanks for that.

To be clear, there are 4 entries in the data validation table (E1246 to E1249) that will be in the cells E9:E20 that I want to use. There will only ever be one of those 4 text strings in the range E9:E20.

Cheers

So you'll need something like...

If(E9=E1246,vlookup(etc),if(E9=E1247,vlookup(etc),if(E9=E1248,vlookup(etc),if(E9=E1249,vlookup(etc),"Pick Something"))))