IF Vlookup

[shepoodle]

I have a spreadsheet that pulls over data based on a code that we have created. So the current Vlookup says that if the cell comes back with a 0, then it will lookup the other code and pull back the name. I could get it to work one way, but not the other. Any suggestions?


=IF(IFNA(VLOOKUP($E3,Usages!$A:$D,4,0),0)=0,(IFNA(VLOOKUP('Cost Savings Sheet'!C3,Usages!$A:$D,4,0),0)),IFNA(VLOOKUP(C3,Usages!$A:$D,4,0)>0,IFNA(VLOOKUP('Cost Savings Sheet'!E3,Usages!$A:$D,4,0),0)))

New Key New Code Old Key Old Code Material Description
3662591095 366259 3543251095 354325 BAG - CRUZ 12CT YELLOW CORN 50
3662811095 366281 3519371095 351937 FALSE
3662821095 366282 3519351095 351935 FALSE

I hope this makes sense.

 

[svendiamond]

=IF(IFNA(VLOOKUP($E3,Usages!$A:$D,4,0),0)=0,(IFNA(VLOOKUP('Cost Savings Sheet'!C3,Usages!$A:$D,4,0),0)),IFNA(VLOOKUP(C3,Usages!$A:$D,4,0)>0,IFNA(VLOOKUP('Cost Savings Sheet'!E3,Usages!$A:$D,4,0),0)))

The first part of your code is saying: if the first VLOOKUP equals zero or equals N/A (which would then return a zero), then do the second VLOOKUP, otherwise do the third VLOOKUP... *so if your first VLOOKUP returns anything other than zero, it's going to skip straight to the third VLOOKUP... which I'm assuming you don't want. In other words the first part is not going to run your first VLOOKUP, it's just going to check whether it's zero or not. Which is fine, if that's what you want...*


so if your first VLOOKUP does not return zero, then it's going to run this part:

IFNA(VLOOKUP(C3,Usages!$A:$D,4,0)>0,IFNA(VLOOKUP('Cost Savings Sheet'!E3,Usages!$A:$D,4,0),0))

...It looks like you meant to use an IF statement here. "IFNA(vlookup)>0" is not a value, it is a condition to check.

Soo maybe this?

=IF(IFNA(VLOOKUP($E3,Usages!$A:$D,4,0),0)=0,(IFNA(VLOOKUP('Cost Savings Sheet'!C3,Usages!$A:$D,4,0),0)),IF(IFNA(VLOOKUP(C3,Usages!$A:$D,4,0)>0,0),IFNA(VLOOKUP('Cost Savings Sheet'!E3,Usages!$A:$D,4,0),0),0))


(...also if you just want any "ISNA" to return a zero you could get rid of all the ISNA's and use a big one over the entire formula)