Combining if and vlookup functions

[SONART]

Hi All,

I'm trying to put together a formula combining "if" and "vlookup" statements.

I have a number of lookupvalues in the following cells which I want to look and derive from a list of codes in column H:

A B C
1 200A 200B 200C

I tried the following formula but for some reason it's not working:

=if(vlookup(A1,H:H,1,false)=A1,A1,if(vlookup(B1,H:H,1,false)=B1,B1,if(vlookup(C1,H:H,1,false)=C1,C1)

It only works if there is a match in cell A1 but if there is no match it's not matching values in cells B1 and C1.

Could someone please help.

Many Thanks,
SONART

 

[Benjaminmin]

Hi All,

I'm trying to put together a formula combining "if" and "vlookup" statements.

I have a number of lookupvalues in the following cells which I want to look and derive from a list of codes in column H:

A B C
1 200A 200B 200C

I tried the following formula but for some reason it's not working:

=if(vlookup(A1,H:H,1,false)=A1,A1,if(vlookup(B1,H:H,1,false)=B1,B1,if(vlookup(C1,H:H,1,false)=C1,C1)

It only works if there is a match in cell A1 but if there is no match it's not matching values in cells B1 and C1.

Could someone please help.

Many Thanks,
SONART

You could try and add an ISNA constraint, something like:

=IF(ISNA(if(vlookup(A1,H:H,1,false)=A1,A1,if(vlookup(B1,H:H,1,false)=B1,B1,if(vlookup(C1,H:H,1,false)=C1,C1)),(vlookup(A1,H:H,1,false)=A1,A1,if(vlookup(B1,H:H,1,false)=B1,B1,if(vlookup(C1,H:H,1,false)=C1,C1),"N/A")

 

[pgc01]

Hi

It only works if there is a match in cell A1 but if there is no match it's not matching values in cells B1 and C1.

If the first VLookup() is not successful then it returns an error, and you are not dealing with it, like Benjamin is doing.

Another option:

=IF(COUNTIF(H:H,A1),A1,IF(COUNTIF(H:H,B1),B1,IF(COUNTIF(H:H,C1),C1,"N/A")))

 

[SONART]

Thanks pgc01! It worked perfectly.

Thanks for taking the time to reply Benjaminmin.

 

[pgc01]

You're welcome. Thanks for the feedback.