Sorry! Yes, I want to find a match over the range (but there may be more
than 1 match within the range). I want the formula to return the column A
value of the same row each time there is a match.

Maybe a clearer example:

Name Item Item Item
A Desk Pen Phone
B Phone Book Bag
C Desk Pen Bag

So I want to lookup "bag" and it should return Persons B and C in an array.

Sorry if this is still confusing! Please ignore the formula I pasted and
just let me know how it should be -- maybe I have it all wrong in the formula
that's why it's confusing....

Thanks.


"Pete_UK" wrote:

Your description is very confusing (to me) - can you re-phrase it?

In your formula you are trying to use MATCH over the range B4:I95, but
you can't do that.

Pete

On Feb 2, 9:50 am, Very lost <Very
wrote:
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.