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

Do you think Adobe should discontinue Flash?

Discuss in The Lounge

Poll History