How to match one list with another?

[Atul Rajratna]

Hi Friends,

I have a list of remarks. If my agents get all these remarks, he will get passed in monthly PKT and if any one of it is missing from it, agent will be failled.
I tried with previous threads, but still need some help. Please..

For Example:
Column A has Remarks values (Which is empty, Manager has to fill the values)
and values are -

A​

Business \ Client sign off
LT2 \ Finance Sign Off
SS Sign Off
One slider Upload
Exam Cleared


so, if anything from the list (A1:A5) is missing or different, the certificaton in the yellow section (cell A6) will reflect FAIL. and if all are same as given list, then result will be PASS.

I tried it by using =countif() and by defining the name range by putting separate list, but is not satisfactory.

Please help me to resolve this.

Thanks!

-Atul Rajratna

 

[NARAYANK991]

Hi Atul ,

Can you please give some worksheet references , such as :

1. What is the address of the cells which has the 5 remarks in column A ? Is it A1:A5 ?

2. What are the addresses corresponding to each agent's remarks ?

Narayan

 

[Atul Rajratna]

here's the file..
Please check.

 

[NARAYANK991]

Hi Atul ,

Try this :

=AND(COUNTIF($A$1:$A$5,$A$15:$A$19))

entered as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[Atul Rajratna]

Thank you so much Narayan Sir..
It's working. Awesome!
But I'm not able to put this formula in IF() statement, as I'm getting the result in "TRUE"/"FALSE" format & I'm trying to get it in "YES"/"NO" format.
can we do it by different way?

Thanks!

 

[NARAYANK991]

Hi Atul ,

Why can you not use it in an IF statement ?

=IF(AND(COUNTIF($A$1:$A$5,$A$15:$A$19)),"PASS","FAIL")

entered as an array formula , using CTRL SHIFT ENTER should work.

Narayan

 

[Atul Rajratna]

Thank you so much Narayan Sir..
It's really very helpful..

Have a great time!

-Atul