Issues with VLOOKUP in Excel

Question

I have a tax table in one sheet that has a list of tax values. For example:

Sheet1:  Tax Tables
    A      B      C
1  Min    Max    Taxed
   -------------------
2  50     100    10
3  100    200    20
4  200    300    30

In another sheet I have a gross income value of say 120 in cell A1. What I want to do is have a vlookup (I'm assuming that's what I should use) that checks cell A1 to see if it's between the Min and Max and then outputs the taxed amount in B1.

Sheet2:  Income
    A      B
1  Gross FedTax
   -----------
2  120   Value from Column C goes here

I already have the sheet in Tax Tables set up with named spaces A:C=Min and B:C=Max I tried doing this:

=AND(VLOOKUP(<A1,Min,3,False),VLOOKUP(>A1,Max,2,FALSE))

But not even close... I just want to check column A in the first sheet to see if it's less than the the value in the second sheet, and check column B in the first sheet against the value in the second for if its more, then put the value in column C in the first sheet into the cell next to the value in the second sheet.

What result do you expect (or want) if the lookup value is < 50 or > 300? — barry houdini
it wont be, these are tax tables and what I'm showing are examples, they start at $1 and max out at $350k, No one I work with makes more than $350k or less than a dollar so it'll be fine — Flynn
OK, no problem - I see you have a good answer from Doug, you can also use LOOKUP like =LOOKUP(D2,A$2:B$5) — barry houdini
yeah I was looking at all the different options and it was driving me nuts how many different ways to go about it with only very minor differences between the options lol. but his change of the vlookup worked perfectly (after some adjustments of my table) — Flynn

Doug Glancy Doug Glancy · Accepted Answer · 2013-04-17T13:32:07

To use a VLOOKUP, put your maximums and minimums in the same column.

enter image description here

Then use the TRUE argument, which means it looks for the next value that matches. Assuming the value you're looking up in D2, you'd put a formula like this in E2:

=VLOOKUP(D2,$A$2:$B$5,2,TRUE)

enter image description here

Issues with VLOOKUP in Excel

2 Answers