Multiple List Values from Criteria

You'll often find yourself looking up individual values with Index Match, Vlookups, HLookups etc.. but what if you've got a list of say salaries with their associated date for a person and you're able to grab all of the other data for that member on changing a dropdown but the salaries... we don't know how many there may be per member and any formula that you may be able to concoct to test on how many matches there are and which value to grab specifically will be long and awful to maintain - assuming that it's possible in the first place!.

Below is a really nice array formula that lets you do everything the above describes and more if you want. Array formulas are a little different to standard formulae in that for them to run properly you'll need to hit Ctrl+Shift+Enter when you want to ext the cell (this is why they're also sometimes referred to as CSE formula).

Lookup = $N$5
LookupRange = $B$4:$B$23   (the range/column you want to search in)
LookupRangeFirstCell = $B$4  
ResultsRange = C4:C23     (the range/column you want this column to pull from)

=INDEX(ResultsRange, SMALL(IF(Lookup=LookupRange, ROW(LookupRange)-ROW(LookupRangeFirstCell)+1), ROW(1:1))) 

=INDEX($C$4:$C$23, SMALL(IF($N$5=$B$4:$B$23, ROW($B$4:$B$23)-ROW($B$4)+1), ROW(1:1)))