Formula to match a value and return multiple results?

[haste]

Hi there,

I'm looking for a formula to match a value within an array and return the results found.

I know how to you use vlookup/match/index formula's, but I can only return the first match found. I would like to return the first match in cell A1 and the second match inside A2, third inside A3, etc.

So when the first match is found and placed inside A1 then the formula inside A2 should ignore the first match that is already found and show the next match value.

I would like to get this done with a formula and not with the autofilter option inside Excel.

I created an example: http://www.haste.nl/formula-filter.zip

Thanks for helping out!

 

[edgillenw]

New to this forum, so sorry I am make a mistake! This solution is perfect for a training database that I'm creating. Basically each employee has a training sheet and then I have a summary sheet that looks at each individual sheet, determines if the training is past due, and returns the name of the training course.

Explanation: I have training due dates in column D and training courses in Column A. This formula determines if the due date in column D is past due, and if so returns to me the training course from Column A. The problem is that some of the cells in column D are intentionally blank. However, if that row has text in column A, the formula returns that text when I don't need it to. I tried adding an extra IF statement to the beginning of your formula as such =IF(D1:D20="","", your formula below) however that doesn't work.

Any suggestions?

Hi and welcome,

Try this Array-formula in B4

=IF(COUNTIF($E$2:$E$17,$B$2)>=ROWS($B$4:B4),INDEX($D$2:$D$17,SMALL(IF($E$2:$E$17=$B$2,ROW($E$2:$E$17)-ROW($E$2)+1),ROWS($B$4:B4))),"")

confirmed with Ctrl+Shift+Enter

copy down

M.

 

[Marcelo Branco]

Hi edgillenw

Welcome to Mr Excel

This thread is old and has many posts. I think you should create a new thread explaining the best you can what you need. Try to post a data sample and expected result(s).

Take a look at
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

It's very helpful and has tips to create your own thread.

M.