searching columns and rows

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.

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

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(A1: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!

Originally Posted by lsu-i-like

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.

Originally Posted by lsu-i-like

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.

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.