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