Unconventional Index-Match-Match question

[scoveg13]

Please use the chart below as reference.

I'm trying to do an If- Index-Match-Match formula based on the chart below. Instead of pulling the numbers, I need to Index the "Gold, Silver, Bronze" column. Example, if Midatlantic has 60 points, then Silver will be the returned result.

=INDEX(U3:W3,MATCH(A20,T4:T12,1),MATCH(E20,U4:W12,1))

where U3:W3 = Gold, Silver Bronze,
a20, t4-t12= Region
e20,U4:W12 = Points.


Region Gold Silver Bronze
Midatlantic 100 60 40
Midwest 150 90 60
Mountain 50 30 20
New England 100 60 40
Northwest 100 60 40
Southeast 150 90 60
Southwest 200 120 80
West 100 60 40
Northeast 100 60 40

Any help would be greatly appreciated.

 

[scoveg13]

Ok. Would you repost the table such that it is not needed to retype it. Is this the second table you talk about?

Yes this is the second table I was referring to.

Total Points
Region Gold Silver Bronze
Midatlantic 240 140 0
Midwest 360 210 0
Mountain 120 70 0
New England 240 140 0
Northwest 240 140 0
Southeast 360 210 0
Southwest 480 280 0
West 240 140 0
Northeast 240 140 0

 

[Aladin Akyurek]

Yes this is the second table I was referring to.

Total Points
Region Gold Silver Bronze
Midatlantic 240 140 0
Midwest 360 210 0
Mountain 120 70 0
New England 240 140 0
Northwest 240 140 0
Southeast 360 210 0
Southwest 480 280 0
West 240 140 0
Northeast 240 140 0

Ok. Thanks. Why did you not put this table also in ascending order?

 

[scoveg13]

Ok. Thanks. Why did you not put this table also in ascending order?

The other table is now in ascending order. I forgot to change this one.

 

[Aladin Akyurek]

The other table is now in ascending order. I forgot to change this one.

We have now...

Row\Col	A​	E​	F​	T​	U​	V​	W​	X​	Y​	Z​	AA​
2​				New Points​	​	​	​		Total Points​	​	​
3​				Region	Bronze	Silver	Gold		Bronze	Silver	Gold
4​				Midatlantic	40​	60​	100​		0​	140​	240​
5​				Midwest	60​	90​	150​		0​	210​	360​
6​				Mountain	20​	30​	50​		0​	70​	120​
7​				New England	40​	60​	100​		0​	140​	240​
8​				Northwest	40​	60​	100​		0​	140​	240​
9​				Southeast	60​	90​	150​		0​	210​	360​
10​				Southwest	80​	120​	200​		0​	280​	480​
11​				West	40​	60​	100​		0​	140​	240​
12​				Northeast	40​	60​	100​		0​	140​	240​
13​
20​	Midatlantic	60​	Silver
21​	Midatlantic	55​	Bronze
22​	Midatlantic	100​	Gold
23​	Midatlantic	40​	Bronze
24​	Midatlantic	30​	#N/A​
25​	Midatlantic	75​	Silver

What would be the values that must occur in T20:T25?

 

[scoveg13]

Ideally, I'd like the values to occur in F20:F25. So two index-match-lookup statements that takes the lower of the metals based on the lower of the points.

Example, the Midatlantic has 60 NEW POINTS and 240 TOTAL POINTS. This is Silver in terms of NEW POINTS and GOLD in terms of TOTAL POINTS. I need the formula to take all of this into consideration and return Silver, as Silver is "Less" than Gold.

 

[Aladin Akyurek]

Ideally, I'd like the values to occur in F20:F25. So two index-match-lookup statements that takes the lower of the metals based on the lower of the points.

Example, the Midatlantic has 60 NEW POINTS and 240 TOTAL POINTS. This is Silver in terms of NEW POINTS and GOLD in terms of TOTAL POINTS. I need the formula to take all of this into consideration and return Silver, as Silver is "Less" than Gold.

I must be missing something...

Midatlantic, 60 >> 60 >> Bronze on New Points, but Midatlantic, 60 >> 0 >> Bronze on Total Points. How did you get Gold?

 

[scoveg13]

I must be missing something...

Midatlantic, 60 >> 60 >> Bronze on New Points, but Midatlantic, 60 >> 0 >> Bronze on Total Points. How did you get Gold?

I apologize for any confusion.

60 New Points = Silver for Midatlantic.

If you look at Midatlantic and look to the right under TOTAL POINTS, you will see 240 TOTAL POINTS = GOLD for the Midatlantic.

 

[Aladin Akyurek]

I apologize for any confusion.

60 New Points = Silver for Midatlantic.

That is clear. We have done that.

If you look at Midatlantic and look to the right under TOTAL POINTS, you will see 240 TOTAL POINTS = GOLD for the Midatlantic.[/QUOTE]

What is the look up value that we use for getting 240 (Gold)? It's certainly not 60.