Good afternoon,

Would anyone have some suggestion on how to proceed with the problem
below?

I have a table organised as follows:

Columns = different customers
Rows = different products
Table cells = sale value of each combination customer-product.

Given one of the sale value in the table, how to find back the
corresponding customer and product names?

This is no problem for an array (i.e. either one customer or one
product) using INDEX / MATCH, but how to do it for a table? A kind of
two-way match or reverse two-way lookup?

Many thanks in advance,

Cheers,

Vincent


--
VincentT
------------------------------------------------------------------------
VincentT's Profile: http://www.excelforum.com/member.php...o&userid=26046
View this thread: http://www.excelforum.com/showthread...hreadid=393930

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, then if
the range of your data, including the headers, is named "dataRange", the
specified sales value is in Cell F1, Column B is the first column of
dataRange and Row 3 is the first row of dataRange:

=INDEX(B:B,INDEX(ArrayMatch(F1,dataRange),1,1)+ROW (dataRange)-1)
for the product name, and

=INDEX(3:3,INDEX(ArrayMatch(F1,dataRange),1,2)+COL UMN(dataRange)-1)
for the customer name

Alan Beban

VincentT wrote:
Good afternoon,

Would anyone have some suggestion on how to proceed with the problem
below?

I have a table organised as follows:

Columns = different customers
Rows = different products
Table cells = sale value of each combination customer-product.

Given one of the sale value in the table, how to find back the
corresponding customer and product names?

This is no problem for an array (i.e. either one customer or one
product) using INDEX / MATCH, but how to do it for a table? A kind of
two-way match or reverse two-way lookup?

Many thanks in advance,

Cheers,

Vincent

What if there are duplicates?

Biff

"Alan Beban" wrote in message
...
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, then if the
range of your data, including the headers, is named "dataRange", the
specified sales value is in Cell F1, Column B is the first column of
dataRange and Row 3 is the first row of dataRange:

=INDEX(B:B,INDEX(ArrayMatch(F1,dataRange),1,1)+ROW (dataRange)-1)
for the product name, and

=INDEX(3:3,INDEX(ArrayMatch(F1,dataRange),1,2)+COL UMN(dataRange)-1)
for the customer name

Alan Beban

VincentT wrote:
Good afternoon,

Would anyone have some suggestion on how to proceed with the problem
below?

I have a table organised as follows:

Columns = different customers
Rows = different products
Table cells = sale value of each combination customer-product.

Given one of the sale value in the table, how to find back the
corresponding customer and product names?

This is no problem for an array (i.e. either one customer or one
product) using INDEX / MATCH, but how to do it for a table? A kind of
two-way match or reverse two-way lookup?

Many thanks in advance,

Cheers,

Vincent

I assume you mean duplicate sales values.

=INDEX($3:$3,INDEX(ArrayMatch($F$1,dataRange),ROW( A1),2)+COLUMN(dataRange)-1)
for the customer name

=INDEX(B:B,INDEX(ArrayMatch($F$1,dataRange),ROW(A1 ),1)+ROW(dataRange)-1)
for the product name

If you enter them in two horizontally adjacent cells and fill down, you
will get the results for each occurrence of the duplicated sales value.

Alan Beban

Biff wrote:
What if there are duplicates?

Biff

"Alan Beban" wrote in message
...

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, then if the
range of your data, including the headers, is named "dataRange", the
specified sales value is in Cell F1, Column B is the first column of
dataRange and Row 3 is the first row of dataRange:

=INDEX(B:B,INDEX(ArrayMatch(F1,dataRange),1,1)+R OW(dataRange)-1)
for the product name, and

=INDEX(3:3,INDEX(ArrayMatch(F1,dataRange),1,2)+C OLUMN(dataRange)-1)
for the customer name

Alan Beban

VincentT wrote:

Good afternoon,

Would anyone have some suggestion on how to proceed with the problem
below?

I have a table organised as follows:

Columns = different customers
Rows = different products
Table cells = sale value of each combination customer-product.

Given one of the sale value in the table, how to find back the
corresponding customer and product names?

This is no problem for an array (i.e. either one customer or one
product) using INDEX / MATCH, but how to do it for a table? A kind of
two-way match or reverse two-way lookup?

Many thanks in advance,

Cheers,

Vincent

Alan,

Many thanks for your functions and your solutions.

I tried them, it is working after removing the "+ROW (dataRange)-1" and
") or "+COL UMN(dataRange)-1" parts. I have not yet understood why
these ROW and COLUMN parts would be necessary and if I keep them I do
not get the right references.

I appreciated your quick and very helpful reply to my problem! Thanks
again.

Have a good day,

Vincent


--
VincentT
------------------------------------------------------------------------
VincentT's Profile: http://www.excelforum.com/member.php...o&userid=26046
View this thread: http://www.excelforum.com/showthread...hreadid=393930

Without knowing where your dataRange is, and *exactly* what formula(s)
worked for for you, it's hard to respond. I don't think you should need
to remove those portions of the formulas, and in fact I believe they are
necessary if your dataRange does not begin at Cell A1. If you post the
information referred to in the first sentence above, I should be able to
clarify it for you.

In any event, thanks for the feedback.

Alan Beban

VincentT wrote:
Alan,

Many thanks for your functions and your solutions.

I tried them, it is working after removing the "+ROW (dataRange)-1" and
") or "+COL UMN(dataRange)-1" parts. I have not yet understood why
these ROW and COLUMN parts would be necessary and if I keep them I do
not get the right references.

I appreciated your quick and very helpful reply to my problem! Thanks
again.

Have a good day,

Vincent