Lookup between two values

[Gareth Brown]

Hi all,

Is there a neater way than using nested ifs to achieve a vlookup BETWEEN two numbers?

ie. have the equivalent of a lookup table except i need a value returned if it is between the numbers:

Min;Max;Return value
60;110;0.455
-10;60;0.318
110;150;0.136
150;(inf);0.091
-(inf);-10;0.000

So if I have 80 (between 60 and 110) 0.455 is returned.

Any ideas?

thanks in advance.

 

[PaddyD]

what if there's more than value between the ranges?

Paddy

 

[Gareth Brown]

Fraid I didn't understand that Paddy!

The lookup table refers to temperatures. I am analysing 1067 pipe temperatures to see which ones are the most critical - if that helps? So yeah, the temps are all over the place (inc. decimals) so I only need to know which lines fall within each bound.
This message was edited by Gareth Brown on 2002-09-17 22:41

 

[RichardS]

Gareth,

You can still use VLOOKUP, but exclude the 0 in the fourth argument, so the match doesn't have to exact. As long as the lookup table is set up correctly, VLOOKUP will return the correct value. See help for the way it works.

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.


Richard

 

[Gareth Brown]

Thanks mate, i'll give it a whirl.

I ended up using Index(..,Match(..,..,1),..) but yeah - works the same.
This message was edited by Gareth Brown on 2002-09-17 23:32

 

[Aladin Akyurek]

What is (inf) ?

 

[PaddyD]

3 options for multi key lookups here:

TOU Vacant Process1.xls
	A	B	C	D	E
1	Min	Max	Returnvalue
2	60	110	0.455
3	-10	60	0.318
4	110	150	0.136
5	150	1.00E+308	0.091
6	-1.00E+308	-10	0
7
8
9
10	CheckValue	80
11	Sumproduct	0.455
12	ArrayedIndex	0.455
13
14	DgetVersion
15	Min	Max	Return
16	<=80	>=80	0.455
17
Sheet3

note:

1) I have changed your (inf) & -(inf) to + and - 9.99999999999999E+307 respectively (this being the largest number excel can hold).
2) If the value can fall between 2 or more sets of ranges (because the ranges are not mutually exclusive), both the index & the sumproduct will give erroneous results. Only the dget will return an error value. (Although you could do some error checking first if you thought this a risk)
3) Dget is a pain to use if you want to check lots of values.


Paddy
This message was edited by PaddyD on 2002-09-17 22:56

 

[Gareth Brown]

Thanks Paddy,
I am impressed at your use of sumproduct to achieve that result - it never occurred to me!

 

[PaddyD]

The caveat re the sumproduct is a big one - only use it if you can guarantee that there will only ever be one match in the range!

Paddy