Fiachracallanan
New Member
- Joined
- Jan 7, 2014
- Messages
- 2
- I am looking to return a sequential list of items which match to an element of text that I have selected. An example will explain what I want better.
- Item Selected Tom
- Ref Amount
- Tom C 32206 3683
- Tom B 36212 34464
- Frank 10552 43642
- Mary 13228 30534
- Tom A 30694 35798
- Mary B 9121 27489
- So I want a Formula which will return the Reference for items in column A which contain the word Tom.
- So the Result I want is:
- Selected
- Tom C 32206 3683
- Tom B 36212 34464
- Tom A 30694 35798
- The formula I have to return an item containing Tom is:
- =INDEX($A$5:$A$10,MATCH("*"&$B$2&"*",$A$5:$A$10,0))
- But this only returns the first Tom and no other.
- The Formula I have to return sequential items with exact reference:
- =INDEX($B$4:$B$11, SMALL(INDEX(($B$2=$A$4:$A$11)*(MATCH(ROW($A$4:$A$11), ROW($A$4:$A$11)))+($B$2<>$A$4:$A$11)*1048577, 0, 0), ROW($A1)))
- This will (if I change the row reference at the end) return all Tom C's (If I change B2 to "Tom C") in sequential order but only the Tom C's and not the other Tom's (Tom B and Tom A). I have tried replacing the $B$2 with "*"&$B$2&"*" but that doesn't seem to work. I would really Appreciate help on this.
- If you want me to clarify further please post here and I will try my best.
Last edited:
