Searching values from table

  • Thread starter Thread starter Tor
  • Start date Start date
T

Tor

I have this problem want to solve:

I would like to search for a value (e.g. Account Number)
and receive a code based on which interval I find the
value in.

I have one column with the "From number", one with the "To
number", and one with the "Code". There are several rows
with different intervals, and different corresponding
Codes.

I then have a value I want to get a code for. If my value
is either the "From number" or the "To number" in a row,
or somewhere in between, I want to get the corresponding
Code as the result. If there are noe match, I will have a
default value (eg. "0").

Example:
From number To Number Code
10000 19999 10
20000 30000 20
30001 35000 30

Then all values from 10000 to 19999 should return 10,
while e.g. 20000 returns 20 and 35000 returns 30. Values
under 10000 and above 35000 should return 0.

Do anybody have a suggestion?

Thank you!
 
Tor,

This is close to what your looking for, better solutions may exist, but here
goes . . .
I set it up as follows

From To Number Code
0 0
10000 10
20000 20
30001 30
35000 0

XXX =VLOOKUP(A8,A2:B6,2)

Where XXX is the lookup value and is stored in A8.

This will return
0 from 0 to 9999
10 from 10000 to 19999
20 from 20000 to 30000
30 from 30001 to 34999
and 0 from 35000 up

Dan E
 
Back
Top