SOLVED

Iron Contributor

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

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.

Many thanks,

7 Replies

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

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

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

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?

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")```

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

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

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

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

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,

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

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

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.

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

Hi Sergei@Sergei Baklan

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

Kind regards,

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

@calof1 , you are welcome