Hlookup with ranking conversion

[ibesmond]

I'm looking for help developing a formula that will return the results...

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

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

<tbody>
</tbody>

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 -

5-24.99

25-49.99

50-199.99

200-399.99

400 or more

<tbody>
</tbody>

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.

 

[ibesmond]

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.