vlookup / match / or function

[percyt]

just a quick question , what is the best approach to the following

i have A1 aus A2 fra & A3 ger

now below in cells A8 to B12 i have

scott	ukr
john	aus
paul	fra
tim	pol
phil	sco

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

and in cells D8 to D12

scott

john

paul

tim

phil

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

In cells E8 to E12 i am looking at the best formula to return "y" if any of column B8:B12 match that of A1:A3

any suggestions? many thanks

 

[Aladin Akyurek]

just a quick question , what is the best approach to the following

i have A1 aus A2 fra & A3 ger

now below in cells A8 to B12 i have

scott	ukr
john	aus
paul	fra
tim	pol
phil	sco

<TBODY>
</TBODY>

and in cells D8 to D12

scott

john

paul

tim

phil

<TBODY>
</TBODY>

In cells E8 to E12 i am looking at the best formula to return "y" if any of column B8:B12 match that of A1:A3

any suggestions? many thanks

E8, control+shift+enter, not just enter, and copy down:

=ISNUMBER(MATCH(INDEX($B$8:$B$12,MATCH($D8,$A$8:$A$12,0)),$A$1:$A$3,0))+0

1 means "y", 0 means "n".

 

[percyt]

thanks aladin i can make that work , greatly appreciated

 

[Aladin Akyurek]

thanks aladin i can make that work , greatly appreciated

You are welcome. Producing 1/0 is better than y/n for you can count the hits with just SUM, for example.