How do I create a list, where I can look-up a corresponding value

[Daiire]

Im am trying to set a system where you would have to example:

Date Data Range Relevent Location

29/10 10 - 20 1
30/10 21 - 42 2


I want to know if I can set a formula in excel to be able to set the data
range (with specific unique values) so that if I look up say data value 19,
it will tell me the location is 1?

 

[Bob Phillips]

If you restructure the data so that you have start points not ranges,

Date Data Range Relevant Location
29/10 10 1
30/10 21 2


you can then use

=INDEX(C2:C3,MATCH(19,B2:B3,1))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Hi,

You could create a table like this which in my case is in A1 - B4
10 1
21 2
43 3
66 4
Note that the left column is sorted ascending and the numbers are your
thresholds
Then use a formula
=VLOOKUP(C1,A1:B4,2,TRUE)

The formula looks in column A for the value in C1. For 10 to 20 it will
return 1 and for 21 on returns 2 etc.

Mike