Index/Match multiple criteria

[=HPSF=RMP]

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
	A	B	C	D	E
1	Category	Rank	Score	Weight
2	A	1	50.00	10.00
3		2	$20.00	30.00
4		3	15.00%	20.00
5		4	12.00%	10.00
6		5	1.39	10.00
7	B	1	35.00	10.00
8		2	$5.00	30.00
9		3	55.00%	20.00
10		4	19.00%	10.00
11		5	4.35	10.00
12
13
14
15
16	SelectCategory:		A
17	Rank	Score	Actual	Weight	PtsEarned
18	1
19	2
20	3
21	4
22	5
23
24	Total			0	0
Sheet1

 

[Oaktree]

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.

 

[=HPSF=RMP]

works perfectly!
thanks!!!

 

[vane0326]

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]

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]

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

 

[=HPSF=RMP]

I think i got it!
thanks