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



"Bob Phillips" wrote in message
...
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"))),LA RGE((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?


"JulieD" wrote in message
...
Hi Bob

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

Cheers
JulieD

"Bob Phillips" wrote in message
...
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:C 4),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)


"Domenic" wrote in message
...

Try the following array formula that needs to entered using
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!

JulieD Wrote:
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


--
Domenic

------------------------------------------------------------------------
Domenic's Profile:
http://www.excelforum.com/member.php...o&userid=10785
View this thread:
http://www.excelforum.com/showthread...hreadid=276012