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.


--
lsu-i-like
------------------------------------------------------------------------
lsu-i-like's Profile: http://www.excelforum.com/member.php...o&userid=14317
View this thread: http://www.excelforum.com/showthread...hreadid=380560

With your sample data in columns A thru F and rows 1 thru 3, I arrived
at 2 solutions: one using an array function and another using
sumproduct. User entry is in cell I1.

The array function is
=SUM(IF(I1=A1:E3,F1:F3,0)) (Invoke the array function by
simultaneously pressing CTRL-SHIFT-Enter)

The Sumproduct answer is
=SUMPRODUCT(--(I1=A1:A3),F1:F3)+SUMPRODUCT(--(I1=B1:B3),F1:F3)+SUMPRODUCT(--(I1=C1:C3),F1:F3)+SUMPRODUCT(--(I1=D1:D3),F1:F3)+SUMPRODUCT(--(I1=E1:E3),F1:F3)

vlookup did work when i set range_lookup to true. i dont understand
why, but it does. i had it set on false because i wanted the exact
number and i dont understand why false would make vlookup only evaluate
the first column.

thank you mr shorty. i would also like to thank those of you with more
creative answers.

ive got my spreadsheet working now almost exactly as i first conceived
it. which is nice.


--
lsu-i-like
------------------------------------------------------------------------
lsu-i-like's Profile: http://www.excelforum.com/member.php...o&userid=14317
View this thread: http://www.excelforum.com/showthread...hreadid=380560

i tried vlookup but it only searches the first column of the table. ill
keep looking.


--
lsu-i-like
------------------------------------------------------------------------
lsu-i-like's Profile: http://www.excelforum.com/member.php...o&userid=14317
View this thread: http://www.excelforum.com/showthread...hreadid=380560

lsu-i-like Wrote:
i tried vlookup but it only searches the first column of the table. ill
keep looking. True it only searches the first column, but with the range_lookup
argument set to the default TRUE (and with the data table sorted like
your sample table), it will still work. From Excel Help Range_lookup is a logical value that specifies whether you want
VLOOKUP to find an exact match or an approximate match. If TRUE or
omitted, an approximate match is returned. In other words, if an exact
match is not found, the next largest value that is less than
lookup_value is returned. If FALSE, VLOOKUP will find an exact match.
If one is not found, the error value #N/A is returned. In other words, if 8 is the lookup value, it looks in the left column
and doesn't find 8. So it decides to use the value in the 6 row
because 8 is between 6 and 11. Unless your real data table is set up
differently than your sample table, VLOOKUP would seem to work for you.


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

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