Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Across Multiple Rows / Columns
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Across Multiple Rows / Columns
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Across Multiple Rows / Columns
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Across Multiple Rows / Columns
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Across Multiple Rows / Columns
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup value across multiple columns and rows | Excel Worksheet Functions | |||
lookup to multiple rows and columns | Excel Worksheet Functions | |||
Using lookup function on multiple rows and columns | Excel Worksheet Functions | |||
Find a value Using a lookup table multiple columns and rows | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions |