2-way match formula?

  • Thread starter Thread starter BillS
  • Start date Start date
B

BillS

First time on site - please excuse any errors of protocol!

I have a 2 dimensional array of elements, all different.

I would like to find a single formula that will search the array for
designated element and return the column in which it appears.
Likewise for the row.

So if the array is

2 5 7
6 8 1

The column formula applied to the argument "7" would return 3
and the row formula would return 1.

This is tantamount to asking for the MATCH function, but in
dimensions.

Thanks...Bil
 
Hi
you may try the following array formulas (entered with
CTRL+SHIFT+ENTER)
1. Return the row
=MAX(IF(A1:C2=7,ROW(A1:C2),0))
2. Return the column:
=MAX(IF(A1:C2=7,COLUMN(A1:C2),0))

or you may use the following non array formulas
1. Return the row
=SUMPRODUCT(MAX((A1:C2=7)*ROW(A1:C2)))
2. Return the column
=SUMPRODUCT(MAX((A1:C2=7)*COLUMN(A1:C2)))
 
Nice answer, Frank!!

Andy.

Frank Kabel said:
Hi
you may try the following array formulas (entered with
CTRL+SHIFT+ENTER)
1. Return the row
=MAX(IF(A1:C2=7,ROW(A1:C2),0))
2. Return the column:
=MAX(IF(A1:C2=7,COLUMN(A1:C2),0))

or you may use the following non array formulas
1. Return the row
=SUMPRODUCT(MAX((A1:C2=7)*ROW(A1:C2)))
2. Return the column
=SUMPRODUCT(MAX((A1:C2=7)*COLUMN(A1:C2)))
Click to expand...
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, with your
illustrated data in B3:C4, the following, array entered into a two
column row will return 1,3 to the respective cells:

=ArrayMatch(7,B3:C4)

Alan Beban
 
Many thanks both.

Very clever Frank - tried to come up with something like that bu
failed!

Bil
 
Hi
in my case the thanks should go to Bob Phillips. I saw this SUMPRODUCT
usage in one of his recent posts :-)
 
Back
Top