search entire column

[nader assaleh]

Hi Gurus

from attached file i am trying to test staff availability F4 against all ID column (B4-19)
what my formula does, it test only against B4 only.

 

[nader assaleh]

what i am trying to achieve is

if d4 value is GF then check all IDs in B4:10 if any has the value of Y from table U4:AC23

if d4 = GF then check ID 15064 from table U4:AC23 value whether it is Y or N
if d4 =GF then check ID 15065 from table U4:AC23 value Y or N
.
.
.
.
.
if d4 value is GF then check ID 18873 from table U4:AC23 value Y or N

 

[bosco_yip]

Modified Output layout and formulas as per attached

Check dropdown list in C2 and E2 as required.

See attachment file.

Regards
Bosco