Hi,

I've read through a number of the discussions but still cannot figure it
out. I have a whole spreadsheet of names that I need to sort through.

For example:

Column A Column B ....... H
X H ..... A
Y B ...... H

Assume Cell D10 is the value to match/lookup. None of the names would
repeat within each row.

I would like the array formula to search through all the rows from column B
and H for cell D10 and return the corresponding value in column A for the row.

So if cell D10 is H, should return X and Y; if D10 is B, should only return Y.

I tried =INDEX($A$4:$I$95,MATCH($A$100,$B$4:$I$95,0),1) (my actual formula)
and many other versions but it keeps returning #N/A. I've tried to put in a
ISNA function but everything shows up blank (there are definitely multiple
matches. Please help! Thanks in advance.