Multiple Indexing
Hi,
I would have a list of data such as; A 4 B 3 C 2 A 5 C 7 A 1 I would like to use some of index and match type function to look through the list and output the values that correspond to A for example. So the output would read A 4 5 1 (horizontally) B 3 C 2 7 Any ideas on what is the best function to use. Index and match I can only get to pick up the first entry? |
Multiple Indexing
use this array formula
=IF(ISERROR(SMALL(IF($A$2:$A$20="A",ROW($B$2:$B$20 )),COLUMN(A1))),"",INDEX($B$2:$B$20,SMALL(IF($A$2: $A$20="A",ROW($B$2:$B$20)),COLUMN(A1))-ROW($B$2)+1)) and copy across -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "LiAD" wrote in message ... Hi, I would have a list of data such as; A 4 B 3 C 2 A 5 C 7 A 1 I would like to use some of index and match type function to look through the list and output the values that correspond to A for example. So the output would read A 4 5 1 (horizontally) B 3 C 2 7 Any ideas on what is the best function to use. Index and match I can only get to pick up the first entry? |
Multiple Indexing
thanks
perfect "Bob Phillips" wrote: use this array formula =IF(ISERROR(SMALL(IF($A$2:$A$20="A",ROW($B$2:$B$20 )),COLUMN(A1))),"",INDEX($B$2:$B$20,SMALL(IF($A$2: $A$20="A",ROW($B$2:$B$20)),COLUMN(A1))-ROW($B$2)+1)) and copy across -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "LiAD" wrote in message ... Hi, I would have a list of data such as; A 4 B 3 C 2 A 5 C 7 A 1 I would like to use some of index and match type function to look through the list and output the values that correspond to A for example. So the output would read A 4 5 1 (horizontally) B 3 C 2 7 Any ideas on what is the best function to use. Index and match I can only get to pick up the first entry? |
Multiple Indexing
Excel 2007 Pivot Table
Data list transform. No array formulas. http://www.mediafire.com/file/vwwmitjyn5o/03_18_09.xlsx |
All times are GMT +1. The time now is 03:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com