Please enable JavaScript to view the comments powered by Disqus.


INDEX(...(...$B$3:$B$14),SMALL(...IF(...(...(...$A$3:$A$14)=$B$15),ROW(...$B$3:$B$14)-2),ROW(...A1)))

How to retrieve multiple values for a given criteria by Vlookup concept

The formula verifies for the cells in the range that matches the given criteria by equating the values, returning the row numbers, that are feed to small to get them one by one. In the index function return the corresponding values from our data.


Used Functions

Category: Lookup and reference

Uses an index to choose a value from a reference or array

Syntax

INDEX(array, row_num, [column_num])

Category: Statistical

Returns the k-th smallest value in a data set

Syntax

SMALL(array, k)

Category: Logical

Specifies a logical test to perform

Syntax

IF(Something is True, then do something, otherwise do something else)

Category: Lookup and reference

Returns the row number of a reference

Syntax

ROW([reference])

Lets chat on this