Lookup between two values

Gareth Brown

Board Regular
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?

Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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

Gareth Brown

Board Regular
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

Well-known Member
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

Board Regular

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

What is (inf) ?

MrExcel MVP

3 options for multi key lookups here:
TOU Vacant Process1.xls
ABCDE
1MinMaxReturnvalue
2601100.455
3-10600.318
41101500.136
51501.00E+3080.091
6-1.00E+308-100
7
8
9
10CheckValue80
11Sumproduct0.455
12ArrayedIndex0.455
13
14DgetVersion
15MinMaxReturn
16<=80>=800.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.

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

Gareth Brown

Board Regular
I am impressed at your use of sumproduct to achieve that result - it never occurred to me!

MrExcel MVP
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!

Replies
5
Views
50
Replies
4
Views
82
Replies
3
Views
158
Replies
4
Views
153
Replies
5
Views
80