Question regarding Excel if Vlookup funtions,

Microsoft Microsoft office excel 2007 ac...
August 25, 2011 at 22:56:49
Specs: Windows XP
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
So basically 12 IFs and VLOOKUPS in the same formula.

Need Help

See More: Question regarding Excel if Vlookup funtions,

Report •

August 26, 2011 at 01:34:11
I can think of 2 different ways:

The hard way would be to nest 12 IF statements together, as follows:


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      2  

Then try this:


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


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

Report •

August 26, 2011 at 02:38:54
Thats great thanks very much, works perfectly!

Report •

Related Solutions

Ask Question