Home

Index and Match Function With "Or" Statement

%3CLINGO-SUB%20id%3D%22lingo-sub-555459%22%20slang%3D%22en-US%22%3EIndex%20and%20Match%20Function%20With%20%22Or%22%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-555459%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20the%20Index%20function%20to%20return%20a%20list%20of%20values%20that%20meet%20one%20of%20multiple%20criteria.%3C%2FP%3E%3CP%3EFor%20Example%3B%20In%20the%20file%20attached%2C%20if%20I%20wanted%20to%20return%20all%20the%20%22Name%22%20values%20that%20match%20the%20%22Numbers%22%20values%20to%20the%20right%20of%20the%20sheet%2C%20how%20would%20I%20do%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20great!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-555459%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-555700%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20Match%20Function%20With%20%22Or%22%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-555700%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339494%22%20target%3D%22_blank%22%3E%40WillyRusss%3C%2FA%3E%26nbsp%3BWhat%20would%20your%20expected%20result%20look%20like%3F%20An%20Index%2FMatch%20will%20return%20only%20one%20value.%20If%20you%20want%20to%20return%20a%20list%2C%20you%20will%20need%20an%20array%20formula%20(unless%20you%20have%20access%20to%20Excel's%20Insider%20build%20with%20the%20new%20Dynamic%20Array%20functions).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-556401%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20and%20Match%20Function%20With%20%22Or%22%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-556401%22%20slang%3D%22en-US%22%3EIf%20I%20understood%20it%20right%2C%20this%20is%20the%20formula%20you%20need%20in%20G3%2C%20copied%20down%20rows%3A%3CBR%20%2F%3E%3DLOOKUP(2%2C%3CBR%20%2F%3E1%2F(VALUE(B%243%3AB%2417)%3DF3)%2C%3CBR%20%2F%3EC%243%3AC%2417)%3CBR%20%2F%3ENote%20that%20the%20values%20in%20B3%3AB17%20are%20left-aligned%2C%20such%20that%20Excel%20reads%20them%20as%20text.%20To%20convert%20them%20to%20numbers%2C%20they%20are%20wrapped%20with%20VALUE%20in%20the%20lookup_vector%20argument%20of%20LOOKUP.%3C%2FLINGO-BODY%3E
WillyRusss
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!

 

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.
Related Conversations