If statement with 2 vlookup ranges

**mkmed** · 10-31-2012, 05:37 PM

Hello,

My current formula is as follows:=IF(A4="","",VLOOKUP(A4,'Sched of Values'!$A$2:$D$65,2,)) and it works fine, but I am trying to add an extra set of parameters and have been unsuccessful. The formula I am trying is as follows: =IF(B5="*",VLOOKUP(B5,Vendors!A2:B1000,1, IF(A5="","",VLOOKUP(A5,'Sched of Values'!$A$2:$D$65,2,)))). This formula does not allow the original part to work (section that refers to A4) when it is false, the formula should force the VLOOKUP, instead regardless of the entries in A4 or B5 it only returns the word false.

I would appreciate any help.

Thanks,
Mark

**amotto11** · 10-31-2012, 05:56 PM

Try This

=IF(B5="*",VLOOKUP(B5,Vendors!A2:B1000,1,FAlSE),IF(A5="","",VLOOKUP(A5,'Sched of Values'!$A$2:$D$65,2,FALSE),""))

**amotto11** · 10-31-2012, 05:56 PM

My last "" is your else statement, if B5 is not * and A5 is not , then it will return "" if you want to return something just add it here.

**mkmed** · 11-01-2012, 11:03 AM

amotto - I got an error for too many arguements for the function. It then highlighted the last two quotes "". I deleted the quotes and the second if/vlookup referring to A5 worked, but the first portion referring to B5 still does not work. Any ideas?

Thanks for the your help on this problem!

**mkmed** · 11-01-2012, 11:15 PM

I think the problem is the wildcard. Is there a way to trigger the if/vlookup if there is any text? I can't look for specific text as it could be multiple entries. Thanks in advance for any help!

Mark

**FDibbins** · 11-02-2012, 01:35 AM

you can test for text with =istext()

but looking at you're 2nd formula, i think you have left off a (

=IF(B5="*",VLOOKUP(B5,Vendors!A2:B1000,1, IF(A5="","",VLOOKUP(A5,'Sched of Values'!$A$2:$D$65,2,))))
should maybe be...
=IF(B5="*",VLOOKUP(B5,Vendors!A2:B1000,1), IF(A5="","",VLOOKUP(A5,'Sched of Values'!$A$2:$D$65,2,)))

**amotto11** · 11-02-2012, 08:55 AM

you could do this

if you were looking for text
=IF(ISTEXT(B5),VLOOKUP(B5,Vendors!A2:B1000,1,FAlSE),IF(ISTEXT(A5),VLOOKUP(A5,'Sched of Values'!$A$2:$D$65,2,FALSE),"A5 and B5 were not text"))

**mkmed** · 11-02-2012, 10:52 AM

Thank you FDibbins and Amotto. I ended up using a form of Amottos. This worked great!! =IF(ISTEXT(B5),VLOOKUP(B5,Vendors!A2:B1000,2,FALSE),IF(A5<1000,VLOOKUP(A5,'Sched of Values'!$A$2:$D$65,2,FALSE)))

Thanks again for the help!
Mark