Advanced vlookup / rank (for 2 dimensions)

[burgie10]

Hi there,

I recently posted this query which was answered perfectly and quickly; http://www.excelforum.com/excel-gene...k-formula.html

However, I was wondering if it was possible to add another dimension to the formula. I have attached a simplified version of the data and was wondering if someone could help me populate (using a formula) the yellow highlighted cells with the answers which are shown in column E.

A match / index formula was used when I had only one score / rank to use however now I have added "Score B" and "Rank B" and need to change when I need use the rank from Score A & Score B. I know it's confusing, hopefully the attachment makes it clearer!

Any help would be really appreciated, and please feel free to ask questions if something isn't clear!

Thanks,
Sam

[adhawan06]

Hi There,

Thanks for visiting us.

I have attached a resulted file for your reference. Please see and let me know if you were looking for the same and it solved your purpose.

[FDibbins]

I am a little confused. In your sample, you leave out a LOT of rankings?

For instance, you dont pull out any even numbered rank for score A ()except 18 and 20), nor almost any odd-numbered ranking for score B? Dont you need to see those?

	I	J
12		Rank
13	Score A	1
14	Score A	3
15	Score A	5
16	Score A	6
17	Score A	7
18	Score A	10
19	Score A	12
20	Score A	13
21	Score A	14
22	Score A	15
23	Score A	17
24	Score A	18
25	Score A	19
26	Score A	20
27	Score B	2
28	Score B	4
29	Score B	8
30	Score B	9
31	Score B	11
32	Score B	16

[adhawan06]

Hi FDibbins,

What I think, he is looking to have unique ranks but form both the category (Score A & B), which is why he left out the even rankings for Score A & Odd ranking for Score B category. Even I was wondering, but as per his request, I thought to give him what he is looking for...

Looking forward to hear from him.

[azumi]

Another try......using array formulas....

[burgie10]

Hi all,
Thanks a lot for your contributions and help, it's greatly appreciated.
You are right, I am looking for the individual scores to be ranked individually. The problem I have now is that because I gave you a simplified version, in the real version there are many many more "scores".
I have attached an example of having more scores. If i was to use the formulas you guys have kindly given me, there would be no room left in the formula box!
Am I making any sense?!!
Thanks
Sam

[FDibbins]

OK so do you want rank 1 for each score or not? Your new file includes more scores, but still misses out rankings?

[burgie10]

Were you able to think of anything FDibbins? Doesn't look like it is possible to do what I am after

Thanks!

[FDibbins]

THis will work, no matter what rank you put in the rank column...
=INDEX($C$1:$V$1,1,MATCH(LARGE(OFFSET($C$1,MATCH($A31,$B$2:$B$17,0),0,1,COUNTA($C$1:$V$1)),B31),OFFSET($C$1,MATCH($A31,$B$2:$B$17,0),0,1,COUNTA($C$1:$V$1)),0))
regular formula, copied down

[burgie10]

I want rank 1 for only score A, rank 2 for only Score B etc etc....
If that's possible?

[adhawan06]

We can't use NESTED IF more than 7 times in excel. Looking forward to give another trix. will try to share the same soon.

[FDibbins]

We can't use NESTED IF more than 7 times in excel. Looking forward to give another trix. will try to share the same soon.

This was a restiction in 2003, but does not apply to later versions. This worked just fine - i stopped at 15...
=IF(B1=1,1,IF(B1=2,2,IF(B1=3,3,IF(B1=4,4,IF(B1=5,5,IF(B1=6,5,IF(B1=7,7,IF(B1=8,8,IF(B1=9,9,IF(B1=10,10,IF(B1=11,11,IF(B1=12,12,IF(B1=13,13,IF(B1=14,14,IF(B1=15,15,"nothing")))))))))))))))

@ burgie - so you are not interested in who ranked 1st in any of teh other scores?

[adhawan06]

This was a restiction in 2003, but does not apply to later versions. This worked just fine - i stopped at 15...
=IF(B1=1,1,IF(B1=2,2,IF(B1=3,3,IF(B1=4,4,IF(B1=5,5,IF(B1=6,5,IF(B1=7,7,IF(B1=8,8,IF(B1=9,9,IF(B1=10,10,IF(B1=11,11,IF(B1=12,12,IF(B1=13,13,IF(B1=14,14,IF(B1=15,15,"nothing")))))))))))))))

@ burgie - so you are not interested in who ranked 1st in any of teh other scores?

Hey FDibbins - Thanks for increasing my knowledge but I use 2013 don't know some time it does not accept...but anyways thanks for this

[burgie10]

OH ok that's a shame, I wonder maybe some others will have another idea!
Thanks for your help.

Anyone!?

[adhawan06]

Hi Burgie...thats all about Excel...There is nothing like which can't be happen / perform in excel. The only thing, there are some restriction and limitation for users so for that we have VBA...

I wish you get your answer soon...

It was GLAD to helped you...



If you think any of us helped you, please say thanks by adding *Add Reputation.