Index and match across columns
 

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.

Index and match across columns
 

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.

Index and match across columns
 

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.

Index and match across columns
 

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",R OW($2:$4)),ROW())))
(validate with Ctrl+Shift+Enter)
and drag down.
HTH
Daniel

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.

Index and match across columns
 

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" wrote:

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",R OW($2:$4)),ROW())))
(validate with Ctrl+Shift+Enter)
and drag down.
HTH
Daniel

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.

Index and match across columns
 

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

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" wrote:

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",R OW($2:$4)),ROW())))
(validate with Ctrl+Shift+Enter)
and drag down.
HTH
Daniel

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.