Multiple VLOOKUP Nested with AND Statement

[anbarblue]

Hi All!

I've been working on this all day! <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am able to get the following two formulas to work independently, but not together… I know I’m missing something, but I cannot figure out what. <o></o>
<o></o>
Here is my pseudocode: If (x= blank, do a vlookup on sheet1, otherwise return x) but if v = “a”, “b”, “c”, “d”, then do a vlookup on sheet2 instead) <o></o>
<o></o>
1.=IF(AA1303="",VLOOKUP(T1303,'MB'!F:G,2,0),AA1303)<o></o>
2.=(VLOOKUP(V1303,'Eastern&Western.Overlap'!A:B,IF(R1303="MINC",2,IF(R1303="RAYASS",2,IF(R1303="RAYJAMES",2,IF(R1303="CAPMKTS",2,IF(R1303="ML",2,FALSE)))))))

Thanks!!!

 

[anbarblue]

Good Morning!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Back to this excel... <o></o>
<o></o>
Here is hopefully a clearer pseudo code. <o></o>
<o></o>
if x=blank, then vlookup dealername in sheet1, and return salesman. If dealername is not found in sheet 1, then look for the name in sheet2 and return salesman. <o></o>
<o></o>
If x <> blank, then return x.<o></o>
<o></o>
I appreciate it! I'm usually pretty good at figuring these out, but I'm stuck on this one.<o></o>

 

[BenMiller]

What version of Excel are you using?

 

[anbarblue]

2010

 

[BenMiller]

if x=blank, then vlookup dealername in sheet1, and return salesman. If dealername is not found in sheet 1, then look for the name in sheet2 and return salesman.

If x <> blank, then return x.

I would say something along the lines of =IF(x="",IFERROR(VLOOKUP(dealername,sheet1reference,col_number,0),VLOOKUP(dealername,sheet2reference,col_number,0)),x)

 

[anbarblue]

Thanks. I'm going to give it a try.

Same formula, but a diversion for a moment… how do I minimize the IF statements that are all the same as mentioned by T. I tried doing an AND statement, I kept getting errors. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
IF(R1303="RAYASS",2,IF(R1303="RAYJAMES",2,IF(R1303="CAPMKTS",2,IF(R1303="ML",2,)<o></o>

 

[BenMiller]

=IF((R1303="RAYASS")+(R1303="RAYJAMES")+(R1303="CAPMKTS")+(R1303="ML"),2)

which will return FALSE if none of the conditions are met.

 

[anbarblue]

I would say something along the lines of =IF(x="",IFERROR(VLOOKUP(dealername,sheet1reference,col_number,0),VLOOKUP(dealername,sheet2reference,col_number,0)),x)

This worked like a charm... I never thought of the "IsError";

Thanks Ben!!! (Do I need to do something on here after there is a solution?)

 

[BenMiller]

Actually it's IFERROR - ISERROR just returns true or false.

And no, but don't worry - once your reply count hits a certain number no one looks at it anyways!

Anyway, glad I could help Appreciate the feedback!

 

[anbarblue]

Yes... IFERROR.

Thanks again, have a great week!