Vlookup

[momoerekson]

Can anyone help me make this work? How can I make the Vlookup return the number on the right? I mannually found each number.. Want it automated.. IF you can open the spreadsheet attachment that is easier to see the problem. THANK YOU!

Top 100 Rank Life Health Auto Bank Mutual F Vlookup
1 $10,011 $5,958 277.1 $1,820 7.5 Life $4,438 50
50 $4,360 $1,840 78.7 $790 2.0 Health $1,453 800
100 $3,696 $1,428 64.7 $357 1.5 Auto 77.00 360
200 $3,027 $1,061 54.3 $248 1.2 Bank $677 559
300 $2,643 $869 48.4 $180 1.0 MutualF 1.70 800
400 $2,307 $731 43.6 $155 0.8 513.8
500 $2,098 $604 40.3 $115 0.7
600 $1,889 $508 37.4 $89 0.6
700 $1,714 $420 37.7 $85 0.6
800 $1,489 $333 32.3 $60 0.5

[abousetta]

Hello and Welcome to the Forum,

Please take a few minutes to read the forum rules and explain what you are trying to accomplish. From a quick look, vlookup is not what you are after but rather Index and Match, but before going any further please explain a bit more.

[DGagnon]

its unclear what you are trying to lookup, and what it is based on, i can open your sheet, but could you please eloborate a bit?

[momoerekson]

I'm trying to find my rank based on my numbers.. so the info on the right is my actual production (number of premium or policies) and i need to know how that ranks in the top 800 people..
So the vlookup should return the rank in the huge table below (or if we could use the small table up top that would work great too!!)

[momoerekson]

SO if i write $4000 in life... then i would be ranked 78 of 800.. does that make sense? I need to konw the rank for each group..
$4438 in life = rank of 50
$50 in health = rank of 800
45.5 in auto = rank of 360
$100 in bank = rank of 559
.5 in mutualf = rank of 800

How can i make those ranks pull automatically?

[DGagnon]

try something like this: =INDIRECT("A"&MATCH(H2,$B$14:$B$814,-1)+13)

[momoerekson]

i'm at work and the attachments are blocked for some reason. can you e-mail that to me? [email protected]

[DGagnon]

believe it or not i dont have e-mail access right now, but if you enter the formula i provided into cell I2 of your sample sheet you should get the result.

then you can copy down to your other rows, but you will need to adjust the range $B$14:$B$814 to the next range as well ($C$14:$C$814) and so on.

also you should not post your e-mail on public forums, as they are easily searched / scaned by spamers.

[momoerekson]

you rock!! thank you!! it works

[abousetta]

Monica may I advise you to remove your email quickly before you get a boatload of spam emails. If you want to give someone your email I would advise you to PM them.