Lookup between two values

Gareth Brown

Board Regular
Joined
Jul 29, 2002
Messages
52
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.
 

Some videos you may like

Excel Facts

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

Gareth Brown

Board Regular
Joined
Jul 29, 2002
Messages
52
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
Joined
Feb 16, 2002
Messages
761
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
Joined
Jul 29, 2002
Messages
52

ADVERTISEMENT

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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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.


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

Gareth Brown

Board Regular
Joined
Jul 29, 2002
Messages
52
Thanks Paddy,
I am impressed at your use of sumproduct to achieve that result - it never occurred to me!
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,542
Messages
5,548,635
Members
410,861
Latest member
Victor96
Top