Index and Match Function With "Or" Statement

Occasional Visitor

Hi All,


I am trying to use the Index function to return a list of values that meet one of multiple criteria.

For Example; In the file attached, if I wanted to return all the "Name" values that match the "Numbers" values to the right of the sheet, how would I do that?


Any help would be great!





2 Replies

@WillyRusss What would your expected result look like? An Index/Match will return only one value. If you want to return a list, you will need an array formula (unless you have access to Excel's Insider build with the new Dynamic Array functions).

If I understood it right, this is the formula you need in G3, copied down rows:
Note that the values in B3:B17 are left-aligned, such that Excel reads them as text. To convert them to numbers, they are wrapped with VALUE in the lookup_vector argument of LOOKUP.