Need to consolidate large quantities of data and vlookup errors out

[bortz]

I've got a large table with horse racing odds that go fron .10-1 t
150-1 in .10 increments with values assigned to those odds, so it look
like this:

A1 B1
.10-1 4
.20-1 4
.30-1 3
etc. all the way up to 150-1.

I tried making a table out of this with 2 columns and my hands wen
numb after inputting about 200 odds entries I tested it out wit
the part I did manage to complete with the vlookup formula and it gav
me some wierd errors for results (maybe because of so many entries)
For example, using the first example above, the user would input .10-
and it would spit out 3 rather than the correct answer of 4. I kno
using an if-then expression with some >x or <x values would do th
trick, having the user input whole numbers rather than odds but I'
making this spreadsheet for somebody else and would rather they had th
user friendlyness of being able to enter horse odds as they ar
accustomed to seeing in the track program. Any ideas of what type o
formula would be best suited to this large quantity of data and stil
maintain the desired odds input format of 1-1,2-1,3-1 etc. would reall
be appreciated.

Thanks,
Fran

 

[Peo Sjoblom]

Did you use exact match in your vlookup?

vlookup(lookup_value,table,index,TRUE/FALSE)

if you leave it or or put 1 or TRUE it will lookup a value but not
necessarily the one you want, if you set it to FALSE it will either return
the exact value or #N/A
However if a user put in 10-1 in the lookup cell without it being formatted
as text it will not find it so make sure it is text, the formula could look
like

=VLOOKUP(A1,C26,2,FALSE)

 

[bortz]

Thanks Peo for the tips. I found the error. When I used the handle to
fill down the formula, it was incrementing the cell numbers in the
formula which don't actually correspond to the starting and ending
cells of the vlookup table.

So my formula looked like this:

=VLOOKUP(G4,H27:I122,2,FALSE)

and the H27 and I122 table begin and end identifiers were being
incremented to
h28 and I123 and so on, when I pulled down the formula to the cells
below it. Silly me, expecting something to work as planned.

I could sure use a more elegant way of doing the odds calculation
without using vlookup so the user can enter odds in x-x format as
described above, though.

Thanks,
Frank

 

[Dave Peterson]

If you always want to point at H27:i122, then change your formula:

=VLOOKUP(G4,$H$27:$I$122,2,FALSE)

The $ mean that that that portion of the address won't change when you copy the
formula.

Since we used $h and $27 and $i and $122, then that range reference will be
frozen.