Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE
SOLVED

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

Iron 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

 

best response confirmed by calof1 (Iron Contributor)
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,

1 best response

Accepted Solutions
best response confirmed by calof1 (Iron Contributor)
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

View solution in original post