# Index/Match multiple criteria

#### =HPSF=RMP

##### Board Regular
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.

works perfectly!
thanks!!!

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.

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
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.

Not sure I understand. Can you post a sample of the actual layout?

I think i got it!
thanks

Replies
0
Views
140
Replies
1
Views
388
Replies
10
Views
415
Replies
1
Views
219
Replies
6
Views
314

1,219,812
Messages
6,150,368
Members
450,956
Latest member
Alan361

### 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.

### Which adblocker are you using?

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

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