Multiple values to be Vlookedup according to a list

[Pofugu]

Hello everybody, and thanks in advance...

I am currently trying to check, according to a list, up to 5 values (dispatched into 5 differents rows, concatenated from other values), and, using a "if" condition, having a "YES" if one of the values is present into my list, or "NO" if it's not.

When the value is first in the row, no problem, of course, but when it's second or more, my formula does not understand and stays into the "#N/A" (not even "NO") of the first row...

[EDIT]: Ah well, it seems that I forgot the "NO"... Can't figure where to insert it...

So, basically:

=IF(
AV3=(VLOOKUP(AV3;'Critical doc items'!B:B;1;FALSE));"YES";
IF(AW3=(VLOOKUP(AW3;'Critical doc items'!B:B;1;FALSE));"YES";
IF(AX3=(VLOOKUP(AX3;'Critical doc items'!B:B;1;FALSE));"YES";
IF(AY3=(VLOOKUP(AY3;'Critical doc items'!B:B;1;FALSE));"YES";
IF(AZ3=(VLOOKUP(AZ3;'Critical doc items'!B:B;1;FALSE));"YES";)))))

AV/AW/AX/AY/AZ are the 5 values

The critical doc Item is my list (1column, 34 rows)

Hope it's clear enough...

Many thanks in advance...

 

[Andrew Poulsom]

Welcome to MrExcel.

Can't you use COUNTIF instead of VLOOKUP, eg COUNTIF('Critical doc items'!B:B;AV3)?

 

[Pofugu]

Welcome to MrExcel.

Can't you use COUNTIF instead of VLOOKUP, eg COUNTIF('Critical doc items'!B:B;AV3)?

Hi Andrew, and thanks for your input.

Did not think about that one... This would do something like:

=IF(
AV3=(COUNTIF('Critical doc items'!B:B;AV3));"YES";"NO")

That ? It won't work even with the value contained in my list, only "NO" is displayed.

*currently trying*

[EDIT] I also have to include my four other rows (AW / AX / AY AZ) in the process...

 

[Andrew Poulsom]

It would be:

=IF(COUNTIF('Critical doc items'!B:B;AV3);"YES";"NO")