Index/Match multiple criteria

=HPSF=RMP

Board Regular
Joined
Jun 9, 2004
Messages
188
Hello,
I've tried to search for the answer, and have found several possibles, but cannot get any of them to work the way I have the table set up.

I want to be able to search the highlighted table on the top. I want to index the category and then the rank. I want to place the score and weight into cells B18:B22 and D18:D22 corresponding to the table above. Is this possible using the table or do I need to make adjustments to it?

thanks
Book2
ABCDE
1CategoryRankScoreWeight
2A150.0010.00
32$20.0030.00
4315.00%20.00
5412.00%10.00
651.3910.00
7B135.0010.00
82$5.0030.00
9355.00%20.00
10419.00%10.00
1154.3510.00
12
13
14
15
16SelectCategory:A
17RankScoreActualWeightPtsEarned
181
192
203
214
225
23
24Total00
Sheet1
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Try:

B18=OFFSET($C$1,5*(INT(MATCH($C$16,$A$2:$A$11,0)-1)/5)+$A18,0)

D18=OFFSET($D$1,5*(INT(MATCH($C$16,$A$2:$A$11,0)-1)/5)+$A18,0)

Copy B18 and D18 down.
 

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
Also,


Input formula in cell B18

=LOOKUP(2,1/($A$2:$A$11=C$16),C2:C$11)


Input formula in cell D18


=LOOKUP(2,1/($A$2:$A$11=C$16),D2:D$11)


Copy both formulas down.
 

=HPSF=RMP

Board Regular
Joined
Jun 9, 2004
Messages
188

ADVERTISEMENT

okay,
Looks like i'm having a problem.
I changed a few things to try and simplify my question and I think in doing so I may have screwed it up.

Category's A&B are actually 150-180 and 180+
Ranks 1-5 are actually names also. (
1=calls
2=Answers
etc, etc.

I'm not sure how the actual formula works, but it looks as if its based on the actual placement of the cells.. They may change so I want them to all be matching the actual names in the cells.

The names may actually change order.
A2:A11 should match C16 (which it does)
B2:B11 should match A18:822 (I dont think it does)?

I hope im explaining this right.

Subtracting 1-5 in the formula wont work because the numbers are actually names.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Not sure I understand. Can you post a sample of the actual layout?
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

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