Need Lookup Value between a Range of Data

[tbakbradley]

I thought I knew how to do this, but I'm receiving a wrong value when the Max value is used as the lookup.

Table:

A B C
Min Max Tier
0 300 0
301 800 1
801 1300 2
1301 1800 3
1801 2300 4

Table has a defined name of "Tiers". Cell G2 has the value I'm using for the lookup. If G2 is between 0 and 300, I need the value "0" displayed in H2. If between 301 and 800, H2 should be "1" and so on.

I have been using this formula in H2: =VLOOKUP(G2, Tiers, 3, True). I thought using "True" would look at a Range.

It works fine as long as the Value in G2 isn't exactly the Max found in the 2nd Column of table "Tiers". So, if G2 = 300, H2 is producing the value of "1" when it should be "0". If G2 = 800, it's pulling back "2" when it should be "1". If 1300, then "3" instead of "2" and so on. If G2 is the Max value in that range, it's producing the Tier from the next Row.

Any ideas?

 

[steve the fish]

I cant replicate what you are saying. If i put 300 in G2 it produces 0. 800 produces 1.

 

[tbakbradley]

I just figured out the problem. I knew this equation would work and have used it many times. The Spreadsheet I'm on has several cells with equations and the cell that should have been used for the Lookup wasn't. I kept missing it when looking at the equation. It was pointing to a cell that contained a "Base Number * 1.4". So, every time I changed the Base Number, the Vlookup didn't produce what I wanted because I was changing the wrong cell I'll chalk this one up for being too early.

 

[Marcílio_Lobão]

tbakbradley, Good morning.

Using your example is not possible to have the same result that you.

Probable the question is on your definition of TIERS (named range).

Is TIERS defined as A2:C6 ?

I hope it helps.