Index and match across columns

[Very lost]

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.

 

[Pete_UK]

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

 

[Very lost]

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.

 

[Daniel.C]

Put the following array formula in row 1 :
=IF(ISERR(SMALL(IF($D$2:$D$4="bag",ROW($2:$4)),ROW())),"",INDEX($A$1:$A$4,SMALL(IF($D$2:$D$4="bag",ROW($2:$4)),ROW())))
(validate with Ctrl+Shift+Enter)
and drag down.
HTH
Daniel

 

[Very lost]

Thanks -- but does this formula checks for column D only? What if I want it
to match for "phone" -- i.e., needs to check column B, C and D to return
Persons A and B?

 

[Daniel.C]

Sorry, I overlooked the problem; try the array formula in row 1 :
=IF(ISERR(SMALL(IF(COUNTIF(OFFSET($A$1:$D$1,ROW($1:$3),0),"phone")>0,ROW($2:$4)),ROW())),"",INDEX($A$1:$A$4,SMALL(IF(COUNTIF(OFFSET($A$1:$D$1,ROW($1:$3),0),"phone")>0,ROW($2:$4)),ROW())))
Drag it down as needed.
HTH
Daniel