Search multiple cells and return text from corresponding match

[middy]

Help please. To keep it simple I have a spreasheet as follows:

A B C D
1 John E E L
2 James E N N
3 Helen N L E
4 Jo L E L
5 Emma L LD E
6 Katie LD L LD

On another spreadsheet I want to lookup cells containing the code "E" or "LD" for each column (B-D) and return the names of the individuals in column A where that criteria is met. I have tried a mixture of the index, if, row and small functions but can't seem to get this to work.

Many thanks in anticipation of your help.

[sailepaty]

Insert a row at the beginning of your source data to start in row 2, then use the following formula in E2 and copy down.

=OR(OR(B2="E",C2="E",D2="E"),OR(B2="LD",C2="LD",D2="LD"))*(MAX($E$1:E1)+1)

Now you can use a normal Index function in your second sheet to get the names.

Regards