j2thea
=IF(ROWS(E$1:E1)<=COUNTIF(Sheet2!$A$1:$A$27,$D$1), INDEX(Sheet2!B$1:B$27,SMALL(IF(Sheet2!$A$1:$A$27=$ D$1,ROW(Sheet2!$B$1:$B$27)-ROW(Sheet2!$B$1)+1),ROWS(E$1:E1))),"")
This formula works perfect, but because I have so much data I'd like to have the keys in more columns than more rows. How can I search both colums and return the information in the column beside it? ID Value ID Value 1 1.2 2 1.3 1 1.6 2 1.7 3 1.4 4 1.5 So if I'm searching for ID 2 it will return 1.3 and 1.7. The main help i need is with returning the value in the 4th column. |
j2thea
Hi!
If I understand what you want then you'd need 2 formulas. One for each group of ID and Value columns. Biff "j2thea" wrote in message ... =IF(ROWS(E$1:E1)<=COUNTIF(Sheet2!$A$1:$A$27,$D$1), INDEX(Sheet2!B$1:B$27,SMALL(IF(Sheet2!$A$1:$A$27=$ D$1,ROW(Sheet2!$B$1:$B$27)-ROW(Sheet2!$B$1)+1),ROWS(E$1:E1))),"") This formula works perfect, but because I have so much data I'd like to have the keys in more columns than more rows. How can I search both colums and return the information in the column beside it? ID Value ID Value 1 1.2 2 1.3 1 1.6 2 1.7 3 1.4 4 1.5 So if I'm searching for ID 2 it will return 1.3 and 1.7. The main help i need is with returning the value in the 4th column. |
All times are GMT +1. The time now is 07:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com