2-way match formula?

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
 
F

Frank Kabel

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)))
 
A

Andy B

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...
 
A

Alan Beban

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
 
B

BillS

Many thanks both.

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

Bil
 
F

Frank Kabel

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