If your reference numbers are actually in ascending order, assuming that
A6 contains your lookup value, try...

=VLOOKUP(A6,A1:F3,6)

Otherwise, try...

=INDEX(F1:F3,MATCH(TRUE,COUNTIF(OFFSET(A1:E3,ROW(A 1:E3)-MIN(ROW(A1:E3)),0,1),A6)0,0))

OR

=INDEX(F1:F3,MATCH(TRUE,(MMULT(--(A1:E3=A6),TRANSPOSE(COLUMN(A1:E3)*0+1))0),0))

The last two formulas need to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!

lsu-i-like Wrote:
on worksheet Data i have a bunch of numbers, each in a cell of their
own, forming something like a matrix.

01 02 03 04 05 0.99
06 07 08 09 10 0.98
11 12 13 14 15 0.97

the first five numbers are reference numbers and the last is actual
information.

on another sheet i have the user enter a reference number and i want to
search through the first 5 columns in all 3 rows (in this example 01 -
15) and return the actual information. if the user enters 1-5 i want
to return 0.99, if the user enters 6-10 i want to return 0.98, if the
user enters 11-15 i want to return 0.97.


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