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 to 150-1 in .10 increments with values assigned to those odds, so it looks 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 went numb after inputting about 200 odds entries I tested it out with the part I did manage to complete with the vlookup formula and it gave me some wierd errors for results (maybe because of so many entries)? For example, using the first example above, the user would input .10-1 and it would spit out 3 rather than the correct answer of 4. I know using an if-then expression with some >x or <x values would do the trick, having the user input whole numbers rather than odds but I'm making this spreadsheet for somebody else and would rather they had the user friendlyness of being able to enter horse odds as they are accustomed to seeing in the track program. Any ideas of what type of formula would be best suited to this large quantity of data and still maintain the desired odds input format of 1-1,2-1,3-1 etc. would really be appreciated.

Thanks,
Frank

[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,C2:D6,2,FALSE)


--

Regards,

Peo Sjoblom

"bortz" <bortz.1zs97a_1134148800.6586@excelforum-nospam.com> wrote in
message news:bortz.1zs97a_1134148800.6586@excelforum-nospam.com...
>
> I've got a large table with horse racing odds that go fron .10-1 to
> 150-1 in .10 increments with values assigned to those odds, so it looks
> 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 went
> numb after inputting about 200 odds entries I tested it out with
> the part I did manage to complete with the vlookup formula and it gave
> me some wierd errors for results (maybe because of so many entries)?
> For example, using the first example above, the user would input .10-1
> and it would spit out 3 rather than the correct answer of 4. I know
> using an if-then expression with some >x or <x values would do the
> trick, having the user input whole numbers rather than odds but I'm
> making this spreadsheet for somebody else and would rather they had the
> user friendlyness of being able to enter horse odds as they are
> accustomed to seeing in the track program. Any ideas of what type of
> formula would be best suited to this large quantity of data and still
> maintain the desired odds input format of 1-1,2-1,3-1 etc. would really
> be appreciated.
>
> Thanks,
> Frank
>
>
> --
> bortz
> ------------------------------------------------------------------------
> bortz's Profile:
http://www.excelforum.com/member.php...o&userid=29437
> View this thread: http://www.excelforum.com/showthread...hreadid=492233
>

[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.



bortz wrote:
>
> 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
>
> --
> bortz
> ------------------------------------------------------------------------
> bortz's Profile: http://www.excelforum.com/member.php...o&userid=29437
> View this thread: http://www.excelforum.com/showthread...hreadid=492233

--

Dave Peterson