# 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=11Then OCSPD=10 etcSo basically 12 IFs and VLOOKUPS in the same formula.Need Help See More: Question regarding Excel if Vlookup funtions,

#1 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:=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 2 ```Then 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.

Report •

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

Report •
Related Solutions 