seeking a smarter lookup function

[Kim M.]

Is there a single function that will look at a table array and see which
values the target number is between and return the appropriate result? There
are many, many values in the table array so if doesnt work.

For example, see the table below. If a particular cell's value is 260, that
would be between 250 and 300 and would return the value to the right in that
column, which is 200

400 450 300
350 400 250
300 350 200
250 300 150
200 250 100
150 200 50
100 150 0
50 100 0
0 50 0

 

[Mike H]

I'm confused but that's easy for me at my age!!

Please re-explain the logic for returning 200 for the number 260 which i
agree is between 250 & 300

Mike

 

[Kim M.]

Sorry. I explained that badly. Let me simplify. Let's say these are sales
numbers. If I sold between $0 and $50 worth of stuff, I get no bonus. So I
want it to return 0. If I sold between $350 and $400 I get a $250 bonus.

With VLookup, I can say, look for this number, go over X columns, and return
the value. But that only works if there is an exact match. I want something
that will look at my table, figure out which set of numbers the starting
number is between, and give me what is in the third column of that row. Does
that make sense?

 

[Rick Rothstein \(MVP - VB\)]

Is there a single function that will look at a table array and see which

values the target number is between and return the appropriate result?
There
are many, many values in the table array so if doesnt work.

For example, see the table below. If a particular cell's value is 260,
that
would be between 250 and 300 and would return the value to the right in
that
column, which is 200

400 450 300
350 400 250
300 350 200
250 300 150
200 250 100
150 200 50
100 150 0
50 100 0
0 50 0

Assuming the columns you show are A, B and C (all starting in row 1) and
that you particular data cell (the 260) is in D1...

=SUMPRODUCT((D1>=A1:A9)*(D1<B1:B9)*C1:C9)

but to make this work, you should fill in the blank values with zeroes.

Rick

 

[Mike H]

Kim,

yes that makes total sense. You need a table of sales and bonuses thus:-
A B
0 0
50 10
350 250
1000 300

Then a vlookup: =VLOOKUP(D1,A1:B2,2,TRUE)

With you sales in d1 until you hit 50 sales it will return zero at 50 it
will return 10
and so on. I.e. until it reaches the value in column A it will return the
lesser amount.

Mike

 

[Kim M.]

Unfortunately that still returns zero.
E5 contains my value that I want to look for, which is 294.
Here is my vlookup:
=VLOOKUP(E5,B5:C13,2,TRUE)
It should return 125.


Here is my table:
400 200 294
350 175 0
300 150
250 125
200 100
150 75
100 50
50 25
0 0

 

[Kim M.]

DOH! I had to put the numbers low to high. Once I did, it worked. Thank you.