looking up a value between two numbers and returning a related value

[JulieD]

Hi All

can't quite figure out what formula to use
if i have the number 27.568 in a cell
and i have a table in sheet2 with the following structure
CODE......LOW.....HIGH
A.......26.258.......26.358
B.......27.259........27.359
C.......27.566........27.666

how do i lookup the 27.568 and return the C (as it falls within this
range) - additionally, sometimes the value i'm looking up might match either
a LOW or HIGH and then the associated code needs to be returned.

Cheers
JulieD

 

[Niek Otten]

Hi Julie,

In general, it is easier to look up a value that is to the right of the
threshold value. You could introduce an extra column, with just "=A1" as
formula.
Also, you don't really need the MAX column.
Let's assume your Code is in column A, Low in column B, High in column C and
a copy of A in D. The value to be looked up is in E1.
Then the formula would be:

=VLOOKUP(E1,B13,2)

If for some reason you can't insert a new column, use a combination of
MATCH() and INDEX() functions.
Post again if you need help with that.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[JulieD]

Oh, and i can't use VLOOKUP as the data in LOW can't be sorted into
ascending order

 

[Niek Otten]

Hi Julie,

So your example was a bit misleading!
I don't think you'll get your requirement done with standard Excel
functions; you probably need a User Defined Function.
To get that right please tell us what to return if values do not fall within
a range and what if ranges overlap.
Maybe it helps if you tell us what the underlying requirement is.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Domenic]

Try the following array formula that needs to entered usin
CONTROL+SHIFT+ENTER...

=INDEX(Sheet2!A2:A4,MATCH(1,(Sheet1!A1>=Sheet2!B2:B4)*(Sheet1!A1<=Sheet2!C2:C4),0))

...where Sheet1!A1 contains your lookup value.

Hope this helps!

 

[duane]

if your list is only 3 rows long you can code this with som
if's.....however if your list can be long then a macro is in orde

 

[JulieD]

Hi Niek

sorry, misleading in that it was in ascending order, but it came with "gaps"


thanks for your input ... its actually a MS Project initated question but i
often think its easier to do the maths in excel than in project. Basically,
the company is using a strange value system to determine priorities of tasks
and they wanted to nominate values for criteria and then return a priority
setting based on these criteria.

after playing around with it a bit more it looks like a vba solution is the
way to go ... there will never be overlapping ranges but there might be gaps
in the ranges and what i guess i was looking for by posting here was to see
if there was some sort of array formula that can be used to lookup between
two columns. (i've not come to grips with what array formulas can & can't be
used for).

i'll play with the vba and post back if i get stuck.

Cheers
JulieD

 

[duane]

here is an example for testing 3 rows codes in a7:a9, lows in b7:b9, hi
in c7:c9

=IF(AND(B4>=B7,B4<=C7),A7,IF(AND(B4>=B8,B4<=C8),A8,IF(AND(B4>=B9,B4<=C9),A9,"N/A"))

 

[JulieD]

Hi Domenic

wow looks promising THANKS

Cheers
JulieD

 

[Bob Phillips]

Julie,

That table looks a bit odd, there are gaps!.

Assuming this is correct, you might want to amend Domenic's formula to cater
for this

=IF(ISNA(INDEX(A2:A4,MATCH(1,(A1>=B2:B4)*(A1<=C2:C4),0))),"No
match",INDEX(A2:A4,MATCH(1,(A1>=B2:B4)*(A1<=C2:C4),0)))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JulieD]

Hi Bob

thanks ...
PS know any good references for learning array formulas?

Cheers
JulieD

 

[Bob Phillips]

Hi Julie,

I think that at the level you will be seeking, no I don't. There is Chip's
page of course, http://www.cpearson.com/excel/array.htm, but this is a basic
primer.

The best place to find out is, you guessed it, here in the NGs. Here you
will find such beauties as this posted by Ken Wright yesterday, which draws
out all the dates between 1970 and 1990 where the 1st April is a Saturday

=IF(LARGE((WEEKDAY(DATE(ROW(INDIRECT("1970:1990")),4,1),2)=6)*(DATE(ROW(INDI
RECT("1970:1990")),4,1)),ROW(INDIRECT("1:21"))),LARGE((WEEKDAY(DATE(ROW(INDI
RECT("1970:1990")),4,1),2)=6)*(DATE(ROW(INDIRECT("1970:1990")),4,1)),ROW(IND
IRECT("1:21"))),"")

Note the use of ROW as a way of indexing a list - this is very useful in
this type of formula.

This is a array formula that also spans an array, that is a formula where
the target cells are all selected and given the array formula at the same
time. A couple of other nice examples of this can be found at Debra's site
http://www.contextures.com/xlDataVal03.html#ValList. Debra uses these with
DV, but the principle applies. I often use the same principle to produce
lists of unique items

There are also a few examples in a paper on my site,
http://www.xldynamic.com/source/xld.LastValue.html/

I hope that these help, but as ever, trial and error is best.

Bob

PS Domenic's formula is good, does it do what you want?

 

[Dave Peterson]

and one more:

Bob Umlas's white paper:
http://www.emailoffice.com/excel/arrays-bobumlas.html

 

[JulieD]

Hi

thanks for the references Bob & Dave i'll check them out when i have a bit
of free time. And yes, thanks, Bob in the 20 test figures, Dominic's
formula worked really well (along with your error handling - saved me some
typing there <vbg>!) i will apply it to the "real" data in the next couple
of days and see how that turns out.

Cheers
JulieD