How can I return multiple employee training record from a huge dataset. Currently I'm only getting one return value using a VLOOKUP

Question

I'm looking to search a user ID and have the return show all training that is in the current dataset..I have included a small snap shot to show what I mean.

I've used a VLOOKUP but it's only returning the one value.

I'm thinking an INDEX MATCH but I'm unsure if it will work. Someone suggested an IFERROR instead?

Answer 1

It is an array formula, so confirming the formula with Ctrl+Shift+Enter.

Please try.

=IFERROR(INDEX($E$2:$E$6, SMALL(IF($A$2:$A$6=I$1, ROW($A$2:$A$6)-1,""), ROW(1:99)), 1), "")

Microsoft reference document:

Guidelines and examples of array formulas