SOLVED
Home

Help using index and match to find value based on 3 criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-727362%22%20slang%3D%22en-US%22%3EHelp%20using%20index%20and%20match%20to%20find%20value%20based%20on%203%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-727362%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20large%20data%20set%20that%20involves%20three%20criteria%2C%20being%20year%2C%20scenario%20and%20product.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20hoping%20to%20use%20a%20formula%20to%20find%20find%20and%20return%20the%20relevant%20answer%20from%20the%20data%20table.%20Attached%20is%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20index%20and%20match%2C%20yet%20to%20find%20the%20right%20combination.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-727362%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730076%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20using%20index%20and%20match%20to%20find%20value%20based%20on%203%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730076%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%2C%3C%2FP%3E%3CP%3Eyou%20could%20use%20the%20SUMPRODUCT%20formula%20instead%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20look%20at%20this%20video%20to%20understand%20how%20to%20use%20it%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dlzjc_eEISe8%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dlzjc_eEISe8%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730306%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20using%20index%20and%20match%20to%20find%20value%20based%20on%203%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730306%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20logic%20is%20bit%20unclear.%20You%20have%203%20criteria%20-%20Scenario%2C%20Stock%20Code%20and%20Year.%20In%20source%20data%20you%20have%20no%20combination%20Stock%20Code%3DFXL%20and%20Year%3D1%2C%20however%20in%20answers%20you%20suggest%205%25%20for%20Year%3D2.%20Why%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20512px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121711i1CFC8EF5D5691272%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20ignore%20above%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(%0A%20%20%20%20INDEX(%24C%2412%3A%24S%2415%2C%0A%20%20%20%20%20%20%20%20MATCH(1%2CINDEX((%24C%2412%3A%24C%2415%3DC%244)*(%24D%2412%3A%24D%2415%3D%24C%243)%2C0)%2C0)%2C%0A%20%20%20%20%20%20%20%20MATCH(TRUE%2CISNUMBER(SEARCH(%24C%242%2C%24C%2410%3A%24S%2410))%2C0))%2C%0A%22N%2FA%22)%3C%2FPRE%3E%0A%3CP%3ECouple%20of%20more%20comments.%20Your%20file%20is%20practically%20not%20downloadable%2C%20perhaps%20due%20to%20comma%20in%20name%20-%20not%20all%20system%20support%20it.%3C%2FP%3E%0A%3CP%3EI'd%20recommend%20not%20to%20use%20Merging%20Cells.%20Potentially%20you%20will%20have%20lot%20of%20issues%20continue%20using%20merging.%20Alternative%20is%20-%20select%20sequential%20horizontal%20cells%20and%20apply%20Center%20Across%20Selection%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20366px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121712i76ED79BB3FA2F310%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730456%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20using%20index%20and%20match%20to%20find%20value%20based%20on%203%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730456%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%20for%20your%20help.%20Your%20solution%20works%20perfectly%2C%20and%20i%20appreciate%20your%20additional%20knowledge%20regarding%20merge%20cells.%20As%20a%20relatively%20new%20excel%20user%20i%20greatly%20appreciate%20all%20information%20and%20assistance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730508%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20using%20index%20and%20match%20to%20find%20value%20based%20on%203%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730508%22%20slang%3D%22en-US%22%3E%3CP%3EHIi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20the%20formula%20i%20notice%20it%20looks%20at%20case%2C%20scenario%2C%20year.%20I%20will%20columns%20for%20min%2C%20median%2C%20max%20and%20sd%20for%20each%20of%20these.%20Can%20this%20criteria%20be%20added%20to%20the%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-733296%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20using%20index%20and%20match%20to%20find%20value%20based%20on%203%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733296%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20simplify%20the%20things%20you%20may%20define%20constants%20in%20Formulas-%26gt%3BName%20Manager%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20350px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121821i07565B187AB451C1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhen%20formula%20for%20the%20median%20is%20modified%20as%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%24C%2412%3A%24T%2415%2C%0A%20%20%20%20MATCH(1%2CINDEX((%24C%2412%3A%24C%2415%3DC%244)*(%24D%2412%3A%24D%2415%3D%24C%243)%2C0)%2C0)%2C%0A%20%20%20%20MATCH(TRUE%2CISNUMBER(SEARCH(%24C%242%2C%24C%2410%3A%24T%2410))%2C0)%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%2BShiftMedian%3C%2FSTRONG%3E%3C%2FFONT%3E)%2C%0A%22N%2FA%22)%3C%2FPRE%3E%0A%3CP%3ESure%20you%20may%20use%20simply%20%2B0%20or%20%2B1%20or%20%2B2%20or%20%2B3%20in%20that%20part%20of%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-733562%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20using%20index%20and%20match%20to%20find%20value%20based%20on%203%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733562%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20kindly%20again%20for%20your%20assistance.%20I%20am%20learning%20a%20lot%2C%20very%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735323%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20using%20index%20and%20match%20to%20find%20value%20based%20on%203%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735323%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

Hi,

 

I have a large data set that involves three criteria, being year, scenario and product.

 

I am hoping to use a formula to find find and return the relevant answer from the data table. Attached is an example.

 

I have tried index and match, yet to find the right combination.

 

Can someone please assist.

 

Many thanks,

 

7 Replies

@calof1 

Hey,

you could use the SUMPRODUCT formula instead:

 

Have a look at this video to understand how to use it: https://www.youtube.com/watch?v=lzjc_eEISe8

 

Quickly learn how Excel's SUMPRODUCT formulas works. Download the workbook: http://www.xelplus.com/excel-sumproduct-formula-easy-explanation/ Get the full course: https://courses.xelplus.com Advanced Excel: Master Excel's SUMPRODUCT Formula This video is part of a series that belongs to my ...
Solution

@calof1 

 

The logic is bit unclear. You have 3 criteria - Scenario, Stock Code and Year. In source data you have no combination Stock Code=FXL and Year=1, however in answers you suggest 5% for Year=2. Why?

image.png

If ignore above formula could be

=IFERROR(
    INDEX($C$12:$S$15,
        MATCH(1,INDEX(($C$12:$C$15=C$4)*($D$12:$D$15=$C$3),0),0),
        MATCH(TRUE,ISNUMBER(SEARCH($C$2,$C$10:$S$10)),0)),
"N/A")

Couple of more comments. Your file is practically not downloadable, perhaps due to comma in name - not all system support it.

I'd recommend not to use Merging Cells. Potentially you will have lot of issues continue using merging. Alternative is - select sequential horizontal cells and apply Center Across Selection

image.png

Hi@Sergei Baklan 

 

Thank you again for your help. Your solution works perfectly, and i appreciate your additional knowledge regarding merge cells. As a relatively new excel user i greatly appreciate all information and assistance.

 

Thanks again

 

HIi@Sergei Baklan 

 

Thanks for your help. 

 

From the formula i notice it looks at case, scenario, year. I will columns for min, median, max and sd for each of these. Can this criteria be added to the formula?

 

Kind regards,

@calof1 

 

To simplify the things you may define constants in Formulas->Name Manager as

image.png

when formula for the median is modified as

=IFERROR(INDEX($C$12:$T$15,
    MATCH(1,INDEX(($C$12:$C$15=C$4)*($D$12:$D$15=$C$3),0),0),
    MATCH(TRUE,ISNUMBER(SEARCH($C$2,$C$10:$T$10)),0)+ShiftMedian),
"N/A")

Sure you may use simply +0 or +1 or +2 or +3 in that part of formula.

Hi Sergei@Sergei Baklan 

 

Thank you kindly again for your assistance. I am learning a lot, very much appreciated.

 

Kind regards,

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies