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
Highlighted

@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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies