ranking formula and a double lookup

**Nafrtiti** · 11-01-2012, 06:48 AM

Hi
I'm looking for 2 formulas - one should rank a bit differently than excels rank
the other should lookup values from a table and not a vector

a detailed example is attached

any suggestions?

thanks

**kbkumar** · 11-01-2012, 07:11 AM

Hi - Could you please recheck the instructions.

"the rank of "1" in a3:d3 is 2. this goes to cell g3..."

The ranges A3:D3 and G3 seem to be headings? Are you referring to 4th row?

Also, how are you generating these ranks?

**Nafrtiti** · 11-01-2012, 07:28 AM

oops, sorry. I did so much editing to the file to make sure its clear that I messed with the rows and column numbers
I fixed it and reattached the file to my original question, with clarifications inside

the ranks are quite similar to excels RANK function. The idea is to find the rank of a number or letter (I know changed to letters to make it less confusing) inside an array. For example, if the array is: {b,a,c,d} then "a" is ranked second

**kbkumar** · 11-01-2012, 08:05 AM

In cell H4 use the below formula and drag it until K9:

Formula:

Please Login or Register  to view this content.

This should solve the first part of your query. Second part still not clear, but let me read it again.

Hope this helps.

**kbkumar** · 11-01-2012, 08:08 AM

Also, please note there is an extra space in Cell H3 after "a", which you need to delete while tring the above.

**kbkumar** · 11-01-2012, 08:15 AM

Try the below formula in cell H21 and drag it until K26

=HLOOKUP(H$3,$O$3:$R$7,MATCH(H4,$N$3:$N$7,FALSE),FALSE)

Hope this is what you had requested for, if not, please let me know.