Unconventional Index-Match-Match question

scoveg13

New Member
Joined
Dec 26, 2014
Messages
25
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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
Try:
Excel Workbook
AETUVW
2
3RegionGoldSilverBronze
4Midatlantic1006040
5Midwest1509060
6Mountain503020
7New England1006040
8Northwest1006040
9Southeast1509060
10Southwest20012080
11West1006040
12Northeast1006040
18
19Silver
20Midatlantic60
Sheet
 

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
748
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi AhoyNC,
I was getting a slight glitch on the use of the formula.
The only way I could get it work with numbers in-between the grid values was to change one of the match types from 0 to 1
and to reverse the table headers from "Gold, Silver, Bronze" to "Bronze, Silver, Gold"

Rich (BB code):
=INDEX($U$3:$W$3,MATCH($E$20,INDEX($U$4:$W$12,MATCH($A$20,$T$4:$T$12,0),0),1))

Paul.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
@Taul,
I think if you change the 1 to a -1 in your formula you will not have to reverse the table headers, but would depend on range for Gold, Silver & Bronze.
Excel Workbook
AEFSTUVW
3RegionGoldSilverBronze
4Midatlantic1006040
5Midwest1509060
6Mountain503020
7New England1006040
8Northwest1006040
9Southeast1509060
10Southwest20012080
11West1006040
12Northeast1006040
13
14
15
16
17
18
19Silver
20Midatlantic55
Sheet
 

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
748
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

Hi AhoyNC,
For Midatlantic, if you score 55 it should return Bronze (as you need 60 or more to get to silver)

Paul
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hi AhoyNC,
For Midatlantic, if you score 55 it should return Bronze (as you need 60 or more to get to silver)

Paul

Looks like you are thinking along the following lines...

Row\Col
A​
E​
F​
T​
U​
V​
W​
3​
RegionBronzeSilverGold
4​
Midatlantic
40
60
100
5​
Midwest
60
90
150
6​
Mountain
20
30
50
7​
New England
40
60
100
8​
Northwest
40
60
100
9​
Southeast
60
90
150
10​
Southwest
80
120
200
11​
West
40
60
100
12​
Northeast
40
60
100
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

Note the reorganization of the table in ascending order!...

In F20 enter and copy down:
Rich (BB code):
=INDEX($U$3:$W$3,MATCH(E20,INDEX($U$4:$W$12,MATCH(A20,$T$4:$T$12,0),0),1)-
    (LOOKUP(E20,INDEX($U$4:$W$12,MATCH(A20,$T$4:$T$12,0),0))>E20))
 

scoveg13

New Member
Joined
Dec 26, 2014
Messages
25

ADVERTISEMENT

Wow thank you everyone! This worked beautifully.

Now there's a Part 2 to this question. I'm using TWO tables, not just one. The table above is the "New Points" criteria. I have a second table that has the same format, regions, points, gold silver bronze, etc, but the points are higher as these are considered "Total" points (add 100 to the New Points to use a theoretical "Total Points" as the values don't matter.

Here's the logic:

If New Points is silver and Total Points is Gold - Default to Silver. If New Points is Gold and Total points is Silver, default to silver. The lower of the "metals" is picked based on the point values. So essentially, it looks like I'd have to use the same index, match, lookup formula again on the "Total Points" table, the issue is integrating the two index-match-lookup formulas WITH the given logic at the start of this paragraph. I hope this makes sense. In my world, I have "New points" at e20, "Total Points" as f20 and The "gold, silver, bronze" - index-match-lookup formula at g20.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
What did you opt for?

Involving your second question, would you post a table of correspondences you seem to describe?
 

scoveg13

New Member
Joined
Dec 26, 2014
Messages
25
What did you opt for?

Involving your second question, would you post a table of correspondences you seem to describe?

I opted for this formula: =INDEX(U3:W3,MATCH(E20,INDEX(U4:W12,MATCH(A20,T4:T12,0),0),1)-(LOOKUP(E20,INDEX(U4:W12,MATCH(A20,T4:T12,0),0))>E20))

cJ3ecqU.png
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Ok. Would you repost the table such that it is not needed to retype it. Is this the second table you talk about?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,908
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top