Thread
:
Lookup Across Multiple Rows / Columns
View Single Post
#
6
Posted to microsoft.public.excel.worksheet.functions
plinius
external usenet poster
Posts: 51
Lookup Across Multiple Rows / Columns
Il 28/08/2012 20:57, plinius ha scritto:
Il 28/08/2012 16:18,
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(B 3:E6))))&"
"&INDIRECT(ADDRESS(SUMPRODUCT((B3:E6=A9)*ROW(B3:E6 )),1))
Hi,
E.
Also:
=INDICE(1:1;MATR.SOMMA.PRODOTTO((B3:E6=A9)*RIF.COL ONNA(B3:E6)))&"
"&INDICE(A:A;MATR.SOMMA.PRODOTTO((B3:E6=A9)*RIF.RI GA(B3:E6));1)
E.
Reply With Quote
plinius
View Public Profile
Find all posts by plinius