display if statement?

Tom Moholland
Tom Moholland used Ask the Experts™
I would like to enter MBH, ERCF, MBR or CBH and then have column a list the matching entries from the data tab
example is what I have entered in to column b here manually
I tried using an if statement but it not working as you can see in cell A4
work.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paste this array formula in A4:

=IFERROR(INDIRECT("Data!B"&SMALL(IF(INDIRECT("Data" & "!$A$2:$A$36")=$A$1,ROW(Data!$B$2:$B$36)),ROW(1:1))),"")

Open in new window

For array formulas you must select Ctrl+Shift+Enter to enter. Copy A4 and past down.

Paul
NorieAnalyst Assistant
Tom

You could try this array formula entered in B4 with CTRL+SHIFT+ENTER and copied down:

=INDEX(Data!$B$2:$B$36,SMALL(IF(INDEX(Data!$A$2:$A$36,,1,1)=Summary!$A$1,MATCH(ROW($A$2:$A$36),ROW($A$2:$A$36)),""),ROWS($B$4:B4)))

Note, you'll get #NUM! errors when you copy the formula down more rows than there are matches.

For example If you had 'ERCF' in A1 and  copied the formula down to B23 you would get errors in B21:B23 as there are only 17 matches for 'ERCF' on the list in the Data sheet.
Here's a working sample.
work120419.xlsx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial