Microsoft Microsoft office excel 2007 ac...

My problem i have a table of 60,000 records in one file and a pricing matrix in another sheet. So far i have =IF(AF2="OXres",VLOOKUP(R2,LIST.xlsx!PP,13,FALSE)) which works but when i add to it like =IF(AF2="OXres",VLOOKUP(R2,LIST.xlsx!PP,13,FALSE)+IF (AF2="OMSPD",VLOOKUP(R2,LIST.xlsx!PP,12,FALSE)) it errors.

I want to add 13 more if vlookup to the one formula, so i want to see if AF2="OMSPD" lookup R2 in List,xlsx and return value in 12 not 13,Then OMLPD=11

Then OCSPD=10

etc

So basically 12 IFs and VLOOKUPS in the same formula.Need Help

I can think of 2 different ways: The hard way would be to nest 12 IF statements together, as follows:

=IF(AF2="OXres",VLOOKUP(R2,LIST.xlsx!PP,13,FALSE),

IF(AF2="OMSPD",VLOOKUP(R2,LIST.xlsx!PP,12,FALSE),

IF(AF2="OMLPD",VLOOKUP(R2,LIST.xlsx!PP,11,FALSE),

etc.)))))))))))The easier way would be to use another VLOOKUP and let the value in AF2 determine the col_num value for your first VLOOKUP:

Create a table with the 12 possible values that might be in AF2 and the col_num associated with that value:

A B 1 OXres 13 2 OMSPD 12 3 OMLPD 11 4 OCSPD 10 ... 12 xxxx 2Then try this:

=VLOOKUP(R2,LIST.xlsx!PP,VLOOKUP(AF2,$A$1:$B$12,2,FALSE),FALSE)

or, since 0 and 1 are equivalent to TRUE and FALSE, save yourself some typing:

=VLOOKUP(R2,LIST.xlsx!PP,VLOOKUP(AF2,$A$1:$B$12,2,0),0)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thats great thanks very much, works perfectly!

Ask Your Question

Weekly Poll