Hlookup with ranking conversion

ibesmond

New Member
Joined
Nov 26, 2010
Messages
17
I'm looking for help developing a formula that will return the results...

1st, 2nd, 3rd, 4th, etc 51st, etc.

[TABLE="width: 1549"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Department[/TD]
[TD]TO[/TD]
[TD] Variance[/TD]
[TD]CC![/TD]
[TD]CC2[/TD]
[TD]CC3[/TD]
[TD]5-24.99[/TD]
[TD]25-49.99[/TD]
[TD]50-199.99[/TD]
[TD]200-399.99[/TD]
[TD]400 or more[/TD]
[TD]YTD[/TD]
[/TR]
[TR]
[TD]02/27/15[/TD]
[TD]Bill[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (99.95)[/TD]
[TD][/TD]
[TD]6th[/TD]
[TD]$50.00-$199.99[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]02/27/15[/TD]
[TD]Sam[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (11.07)[/TD]
[TD][/TD]
[TD]15th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]April[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (9.76)[/TD]
[TD][/TD]
[TD]8th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Scott[/TD]
[TD]CHEF[/TD]
[TD]Outlet Manager[/TD]
[TD] 14.00[/TD]
[TD][/TD]
[TD]10th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Randy[/TD]
[TD]SERVER[/TD]
[TD]Food Manager[/TD]
[TD] (14.00)[/TD]
[TD][/TD]
[TD]3rd[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Sarah[/TD]
[TD]SERVER[/TD]
[TD]Food Manager[/TD]
[TD] 17.00[/TD]
[TD][/TD]
[TD]2nd[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Glenn
[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (5.01)[/TD]
[TD][/TD]
[TD]26th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Larry
[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] 106.00[/TD]
[TD][/TD]
[TD]28th[/TD]
[TD]$50.00-$199.99[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]87[/TD]
[/TR]
</tbody>[/TABLE]

Date, Name and Variance are input cells.
Department and TO are Vlookups. I just created a formula for CC3 which is: =IF(E2="","",VLOOKUP(ABS(E2),$P$2:$Q$8,2,TRUE)). It looks up the variance and determines which range to return.

I wanted to create a formula for CC2 which I assume would use a Hlookup (The Column headers -
[TABLE="width: 1549"]
<tbody>[TR]
[TD]5-24.99[/TD]
[TD]25-49.99[/TD]
[TD]50-199.99[/TD]
[TD]200-399.99[/TD]
[TD]400 or more[/TD]
[/TR]
</tbody>[/TABLE]

Then lookup the corresponding amount in the table. EG. The first example is between 50-199.99. The table states it to be 6 as the result. Then the formula would convert the 6 to 6th.

I found a couple formula's that I think might be able to be combined with an hlookup but I'm not sure.

Where A1 would be the number to be converted into a ranking.
=A1&
IF(AND(MOD(A1,10)<4,OR(A1<10,A1>13)),MID("thstndrd",1+MOD(A1,10)*2,2),"th")

Thank you for taking the time to read.
 
Last edited:
So I figured out a working formula. Thank you for the views.

I had to add a reference for the hlookup to change the formula for each row. And add the $ signs and .00 to get the hlookup to match the lookup value.

=IF($E2="","",(HLOOKUP($H2,$I$1:$N$31,$O2,FALSE))&IF(AND(MOD((HLOOKUP($H2,$I$1:$N$31,$O2,FALSE)),10)<4,OR((HLOOKUP($H2,$I$1:$N$31,$O2,FALSE))<10,(HLOOKUP($H2,$I$1:$N$31,$O2,FALSE))>13)),MID("thstndrd",1+MOD((HLOOKUP($H2,$I$1:$N$31,$O2,FALSE)),10)*2,2),"th"))

Hope you find it useful.
 
Upvote 0

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
Back
Top