INDEX and MATCH

[Slickabc123]

I had a question regarding INDEX and MATCH. Let’s say we have I have multiple sub tables within a table. Can the INDEX command be used to narrow down the result down using Data Validation? For example, in one table I have “Produce” which then branches off to “Apple” and “Oranges”, from there “Apple” branches off into an additional two columns "25 deg" and "20 deg" and that branches off into an additional to columns “Refrigerator 1” and “Refrigerator 2” same thing with the oranges. I tried using a combination of Data Validation, Index, and Matching, but I can’t get it to work.

My goal is to have the user select the Sote Number, Produce, Type, Temp, and Ref Number and have excel spit out the proper Value.


=INDEX((B5:I13),MATCH(M7,O5:O13,0),MATCH(M9,Q5:Q6,0))

Im not sure if i have to break it down into individual tables to make it easier. I also meged some cells together so i dont know if that part of the problem.


Is there a turorial that you guys could point me towards in order to help me better understand this?

Thanks

[ConneXionLost]

How about this:

Please Login or Register  to view this content.

[jeffreybrown]

Here is a possible solution if you are willing to add tow extra rows.

Add a new row 3 >> Take Fruit and place it in B3:E3 >> Take Vegtable and place it in F3:I3

Now add a new row 5 >> Take the 20°C and place it in B5:E5 >> Take the word Vegtable and place it in F5:I5

Now in M17 >> =INDEX($B$7:$I$15,MATCH(1,IF($A$7:$A$15=$M$9,1)),MATCH(1,IF($B$3:$I$3=$M$11,IF($B$5:$I$5=$M$13,IF($B$6:$I$6=$M$15,1)))))

IMPORTANT

• This is an array formula
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually

[Slickabc123]

Hi ConneXionLost,

That worked to perfection!

Appreciate your help so much

Thank You

[Slickabc123]

What’s the purpose of the "+" in front of the match and the "-2" in the end of the command?

[ConneXionLost]

They are used for addition and subtraction.