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? |
Lookup Across Multiple Rows / Columns
|
Lookup Across Multiple Rows / Columns
|
Lookup Across Multiple Rows / Columns
|
Lookup Across Multiple Rows / Columns
|
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. |
All times are GMT +1. The time now is 09:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com