Forum Discussion

WillyRusss's avatar
WillyRusss
Copper Contributor
May 09, 2019

Index and Match Function With "Or" Statement

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

excel
Formulas and Functions

2 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    May 09, 2019
    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.
  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    May 09, 2019

    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).