VLOOKUP Question

  • Thread starter Thread starter BobA
  • Start date Start date
B

BobA

This is part of a table N2:P137

60 $186.00 $189.00 $25.00
61 $189.00 $192.00 $26.00
62 $192.00 $195.00 $26.00

I use this formula in F12:

=IFERROR(VLOOKUP(ROUND(C14,0),N2:P137,3,1),"")

This is the value in C14--$189.27

This formula returns a value of $26.00, but I want it to return $25.00.

What am I doing wrong?
 
This is part of a table N2:P137
60 $186.00 $189.00 $25.00
61 $189.00 $192.00 $26.00
62 $192.00 $195.00 $26.00

I use this formula in F12:

=IFERROR(VLOOKUP(ROUND(C14,0),N2:P137,3,1),"")

This is the value in C14--$189.27

This formula returns a value of $26.00, but I want it to return
$25.00.

What am I doing wrong?
Click to expand...

It returns the correct result since the value is between $189.00 and
$192.00. The lookup function searches the 1st col of the table and
returns the 3rd col value. To lookup thw 2nd col of the table, change
the lookup range to M2:P137 so that col becomes the 1st col in the
table.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Oops.., that change should be as follows...

=IFERROR(VLOOKUP(ROUND(C14,0),O2:P137,2,1),"")

...where the return is now from col2.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
This is part of a table N2:P137



60 $186.00 $189.00 $25.00

61 $189.00 $192.00 $26.00

62 $192.00 $195.00 $26.00
Click to expand...

I changed your table to this and it returns 25.

$186.00 $189.00 $25.00
$190.00 $192.00 $26.00
$193.00 $195.00 $27.00

Regards,
Howard
 
Hi Bob,

Am Sat, 16 Aug 2014 17:45:31 -0700 (PDT) schrieb BobA:
=IFERROR(VLOOKUP(ROUND(C14,0),N2:P137,3,1),"")
Click to expand...

another important thing:
Your column N has to be sorted ascending


Regards
Claus B.
 
Hi Bob,

Am Sun, 17 Aug 2014 17:36:25 +0200 schrieb Claus Busch:

if the value to look for is in column O you have to change the formula
like Garry wrote in his second answer.
If the value is in column N and column N is sorted ascending your
VLOOKUP gives you the correct result.
If you want 25 as result try:
=INDEX(P:P,MATCH(C14,N2:N137,1))


Regards
Claus B.
 
Hi Bob,
Am Sun, 17 Aug 2014 17:36:25 +0200 schrieb Claus Busch:


if the value to look for is in column O you have to change the
formula like Garry wrote in his second answer.
If the value is in column N and column N is sorted ascending your
VLOOKUP gives you the correct result.
If you want 25 as result try:
=INDEX(P:P,MATCH(C14,N2:N137,1))


Regards
Claus B.
Click to expand...

IMO the lookup table is poorly structured/designed!
Normally, a 'range' lookup chart/table would not start the next range
at the same value as the end of a previous range. For example...

186.00 188.99 25.00
189.00 191.99 26.00
192.00 194.99 27.00

...where the return values are not repeated as in the OP's original
post. That scenario would normally table as...

186.00 188.99 25.00
189.00 194.99 26.00

...where 189.27 correctly would/should return 26.00!

Given the value posted for C14, it escapes me why the OP wants to
deliberately return the wrong value???

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
IMO the lookup table is poorly structured/designed!

Normally, a 'range' lookup chart/table would not start the next range

at the same value as the end of a previous range. For example...



186.00 188.99 25.00

189.00 191.99 26.00

192.00 194.99 27.00



..where the return values are not repeated as in the OP's original

post. That scenario would normally table as...



186.00 188.99 25.00

189.00 194.99 26.00



..where 189.27 correctly would/should return 26.00!



Given the value posted for C14, it escapes me why the OP wants to

deliberately return the wrong value???
Click to expand...

OK, the lookup "table" is a tax table taken down verbatim.

I don't want to look up the wrong answer. What I want to do is have the table return the value that matches the taxable income (rounded up or down)without going over.

In other words, if the taxable income is $189.49 (rounded down to $189.00 then the tax for that amount is $25.00 not $26.00 because the value falls between $186 and $189, not between $189 and $192. (If it were $189.51, then the value would round up to $190 and the tax would be $26.00.)

I'll play around with the answers later and see how they work out. Thanks to all for the help.
 
Claus' Index/Match formula might be a better way to go since it will
always grab the 1st match in the search range. Also, 189.51 should
return the expected $26!
Click to expand...

Well.., I tested this and it still returns $25. It returns $26 when the
INDEX() range matches the MATCH() range.

Also, the tax rate calcs to be more like sales tax than income tax. Can
you explain what this tax actually is?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Well.., I tested this and it still returns $25. It returns $26 when the

INDEX() range matches the MATCH() range.



Also, the tax rate calcs to be more like sales tax than income tax. Can

you explain what this tax actually is?
Click to expand...
Garry
Click to expand...

SINGLE Persons--DAILY Payroll Period
(For Wages Paid through December 2014)
 
IRS in the good'ol US of A.
http://www.prudata.com/LinkClick.aspx?fileticket=WHyatHJlnlM=&tabid=101&mid=585

(Page 19)
Click to expand...

Thanks, Bob!
I now see why it's structured that way; the 2nd amount is the "less
than" part. Makes sense as this table appears to be for those that
can't work with the percentage math format.

According to your link, the correct return for the value in C14 is
indeed $26 since it's NOT -'at least' $186 and 'less than' $189-!

I also see why the same return value appears in multiple rows; it has
to do with how the tax applies based on the # of withholding allowances
claimed.

I suggest you restructure your lookup table to not show decimal places,
and enter the 'less than' amounts minus 1 cent so your project works
correctly with IRS guidelines! (The value will display rounded up) This
should obviate any need to use the ROUND() function!

You might also want to include methodology that automatically returns
from the correct withholding allowance column.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Claus,
If you copy the table BobA links to starting in N3, your formula works
correctly if modified as follows...

=INDEX($Q3:$Q138,MATCH(C14,N3:N138,1))

...where the number of Withholding Allowances is 1. This will be colQ as
the return value of INDEX(). MATCH() returns 60 (Row62) as the position
of the search value.

The table has 13 cols; 2 for the wage ranges, 0 to 10 for Withholding
Allowance cols. I named the table with local scope and revised Bobs
lookup formula as follows...

=VLOOKUP(C14,TaxTable,4,1)

...where the return is from the 4th col (WA1) of the table. Both
formulas now return identical values when C14 is revised. Not sure,
though, how the rest of the sheet is laid out. I'm guessing a list of
names and wages paid, but this could be on another sheet. Would be nice
to have a sample file to work with...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,

Am Mon, 18 Aug 2014 01:53:21 -0400 schrieb GS:
If you copy the table BobA links to starting in N3, your formula works
correctly if modified as follows...

=INDEX($Q3:$Q138,MATCH(C14,N3:N138,1))
Click to expand...

you are right. I did not pay attention that the column N starts in row
2.


Regards
Claus B.
 
Thank you Claus and Gary. This is the first formula I tried, and it does exactly what I want. Appreciate the help.
Click to expand...

Oops, I spoke too soon. It doesn't quite work when rounding is taken into account.

I'll play around with it tomorrow and see if I can get it to work. If I can't I'll come back for some more help.
 
Oops, I spoke too soon. It doesn't quite work when rounding is taken
into account.

I'll play around with it tomorrow and see if I can get it to work.
If I can't I'll come back for some more help.
Click to expand...

Rounding is not needed if you *properly* reconstruct your tax table
as I explained earlier. Your approach to using ROUND() has nothing to
do with how IRS wants the table used. (I have these tables working
correctly already and so I know what I'm talking about. I also know
what you're trying to do!<g>)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Bob,

Am Mon, 18 Aug 2014 18:14:09 -0700 (PDT) schrieb BobA:
Oops, I spoke too soon. It doesn't quite work when rounding is taken into account.
Click to expand...

try:
=INDEX(P:P,MATCH(ROUND(C14,0),N1:N137,1)-1)

Take care that you use N1!
Don't you have to use ROUNDDOWN to the next smaller value?


Regards
Claus B.
 
Don't you have to use ROUNDDOWN to the next smaller value?

The IRS table he's working with do not require rounding search
values if the table is properly configured to IRS guidelines. Not sure
why Bob is 'hung up' on the rounding!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top