Lookup Across Multiple Rows / Columns

[marksolesbury]

hey

I need to look up a value from an group of cells across rows and sheets, and return the top and left hand value from the location of the result.

Eg:

One Two Three Four

Left a b c d
Right e f g h
Up i j k l
Down m n o p


If I searched for 'f', I need to return 'Two Right'. 'p' should return 'Four Down'

I have tried various match and index formulas with no success.

Any ideas?

 

[marksolesbury]

Damn formatting

http://i1212.photobucket.com/albums/cc458/marksolesbury/Firefox Plugin/Untitled-11.jpg

 

[marksolesbury]

 

[marksolesbury]

http://i1212.photobucket.com/albums/cc458/marksolesbury/Firefox Plugin/Untitled-11.jpg

 

[plinius]

Il 28/08/2012 16:18, (e-mail address removed) ha scritto:

hey

I need to look up a value from an group of cells across rows and sheets, and return the top and left hand value from the location of the result.

Eg:

One Two Three Four

Left a b c d
Right e f g h
Up i j k l
Down m n o p


If I searched for 'f', I need to return 'Two Right'. 'p' should return 'Four Down'

I have tried various match and index formulas with no success.

Any ideas?

Insert in A9 "f"

in B9:
=INDIRECT(ADDRESS(1,SUMPRODUCT((B3:E6=A9)*COLUMN(B3:E6))))&"
"&INDIRECT(ADDRESS(SUMPRODUCT((B3:E6=A9)*ROW(B3:E6)),1))

Hi,
E.

 

[plinius]

Il 28/08/2012 20:57, plinius ha scritto:

Il 28/08/2012 16:18, (e-mail address removed) ha scritto:


Insert in A9 "f"

in B9:
=INDIRECT(ADDRESS(1,SUMPRODUCT((B3:E6=A9)*COLUMN(B3:E6))))&"
"&INDIRECT(ADDRESS(SUMPRODUCT((B3:E6=A9)*ROW(B3:E6)),1))

Hi,
E.

Also:

=INDICE(1:1;MATR.SOMMA.PRODOTTO((B3:E6=A9)*RIF.COLONNA(B3:E6)))&"
"&INDICE(A:A;MATR.SOMMA.PRODOTTO((B3:E6=A9)*RIF.RIGA(B3:E6));1)

E.