Index and Match Function With "Or" Statement

Copper Contributor

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!

 

Thanks,

 

Will

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:
=LOOKUP(2,
1/(VALUE(B$3:B$17)=F3),
C$3:C$17)
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.