Combining If, And, Isnumber and 2 vlookups

[ryland00]

Hello,

I am trying to figure out how to do this and I am sure it's probably an easy fix but I have two formulas...

=IF(AND(ISNUMBER($I2),VLOOKUP($P:$P,Key!AD:AE,2,0)="Yes"),"Yes","No")

=VLOOKUP($P2&$I2,Key!Z:AA,2,0)

The first formula is saying that if the value in i2 is a number AND when the vlookup returns with a value of "Yes", then say "Yes", else "No"

The second formula is just a standard vlookup checking two values combined against a key where I have a set of values to check against.

I am just trying to combine the two so that instead of "No" in the first formula, I could put the vlookup there... Like using an IFERROR... But I just can't seem to figure out how to make it work. The values that don't meet the first two criteria just result in an NA.

Any help is greatly appreciated!

Ryan

 

[Aladin Akyurek]

Try...

=IF(AND(ISNUMBER($I2),VLOOKUP($P:$P,Key!AD:AE,2,0)="Yes"),"Yes",VLOOKUP($P2&$I2,Key!Z:AA,2,0))

 

[ryland00]

Try...

=IF(AND(ISNUMBER($I2),VLOOKUP($P:$P,Key!AD:AE,2,0)="Yes"),"Yes",VLOOKUP($P2&$I2,Key!Z:AA,2,0))

Definitely tried that already, but gave it a try again and resulted in only NA's and "Yes". Should only be "Yes" or return whatever is in 'AA' in the Key.

 

[Dryver14]

Are you really looking up the whole of column P in the first lookup? does that formula work on its own?

 

[ryland00]

Are you really looking up the whole of column P in the first lookup? does that formula work on its own?

Well, it works for what I need right now, just trying to figure out something that combines the two different formulas . I have the two separated into two separate columns right now, and both have either "Yes" or NA.

Just tried this too, thinking it would work, and it did not...

=IF(OR(VLOOKUP($P2&$I2,Key!Z:AA,2,0)="yes",AND(ISNUMBER($I2),VLOOKUP($P2,Key!AD:AE,2,0)="Yes")),"Yes","No")

 

[Drrellik]

When using Vlookup with multiple criteria you may have to add a helper cell and concatenation the two lookup cells, see this post from 2006

https://www.mrexcel.com/forum/excel-questions/214177-vlookup-multiple-criteria.html

This might help with the N/A I even tried VLOOKUP(($C6&$C5),D5:E9,2,0) Extra () to have the operation happen first but Vlookup still did not like it until I added the extra cell.

 

[ryland00]

When using Vlookup with multiple criteria you may have to add a helper cell and concatenation the two lookup cells, see this post from 2006

https://www.mrexcel.com/forum/excel-questions/214177-vlookup-multiple-criteria.html

This might help with the N/A I even tried VLOOKUP(($C6&$C5),D5:E9,2,0) Extra () to have the operation happen first but Vlookup still did not like it until I added the extra cell.

Yes, column Z in the Key is a combination that matches what would be in P and I.

 

[Drrellik]

Yes, column Z in the Key is a combination that matches what would be in P and I.

create a cell =P2&i2

THEN change your vlookup(new cell,2,0) and see if the N/A goes away

 

[Drrellik]

=VLOOKUP($P2&$I2,Key!Z:AA,2,0) = N/A

cell P1 = $P2&$I2

=VLOOKUP(P1,Key!Z:AA,2,0) = Answer

-HTH

 

[Aladin Akyurek]

Definitely tried that already, but gave it a try again and resulted in only NA's and "Yes". Should only be "Yes" or return whatever is in 'AA' in the Key.

If each of these individually works:

=IF(AND(ISNUMBER($I2),VLOOKUP($P:$P,Key!AD:AE,2,0)="Yes"),"Yes","No")

=VLOOKUP($P2&$I2,Key!Z:AA,2,0)

and the second must be invoked instead of returning "No" from the first, we would then get when combined:

=IF(AND(ISNUMBER($I2),VLOOKUP($P2,Key!AD:AE,2,0)="Yes"),"Yes",VLOOKUP($P2&$I2,Key!Z:AA,2,0))

The first VLOOKUP might end up in #N/A. If that happens, the combined formula would also return #N/A.

What would you want to happen instead of #N/A?