Search an array with an array and return matching value

[cheal2]

Hi,

I'm stuck on how to get search one array with another array and get the matching result of the two arrays:

Array 1, A1:C1;
Bob
Bill
Ben

Array 2, E1:E3;
Luke
Ben
Han

In D1 I should have the result: Ben

Any help would be greatly appreciated.

[Tony Valko]

Try this...


Data Range

	A	B	C	D	E
1	Bob	Bill	Ben	Ben	Luke
2					Ben
3					Han
4

This array formula** entered in D1:

=INDEX(E:E,SMALL(IF(A1:C1=E1:E3,ROW(E1:E3)),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.