CODEWITHSUNDEEP
excelexcel-formula
Pavlo Bazhenov
Pavlo Bazhenov

Reputation: 3

Excel Conditional data selection by formula

Stuck on looks-easy task. I have a table like that:
AAAA spoon
BBBB fork
CCCC stick
BBBB digger
AAAA apple
BBBB nail
AAAA cap
CCCC watermelon
CCCC banana
BBBB slush
AAAA pie
CCCC tin-plate
AAAA raspberries

And need to select only "AAAA" values to another table: spoon
apple
cap
pie
raspberries
An need to do it dinamically by means of formula, so it will updates each time first table updates. It is not so difficult with VBA, but need formula.

Upvotes: 0

Views: 635

Answers (1)

bbishopca
bbishopca

Reputation: 296

I assumed that your entries where in two columns. For this equation I put them in B and C starting at row 2. I used F1 to enter the string you want to sort by.

=IFERROR(INDEX($C$2:$C$14, SMALL(IF($F$1=$B$2:$B$14, ROW($B$2:$B$14)-MIN(ROW($B$2:$B$14))+1, ""), ROW(C1))), "")

You have to paste this equation in as many rows as the number of answers you expect. If you expect 20 results paste this into 20 rows and the last ROW(C1) should auto increment. The IFERROR keeps the cell blank if no match is included.

Also after entering the equation you have to do Ctrl+Shift+Enter

Upvotes: 1

Related Questions