searching columns and rows

[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.

 

[lsu-i-like]

i tried vlookup but it only searches the first column of the table. il
keep looking. :confused

 

[Domenic]

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!

 

[MrShorty]

i tried vlookup but it only searches the first column of the table. il
keep looking. True it only searches the first column, but with the range_looku

argument set to the default TRUE (and with the data table sorted lik
your sample table), it will still work. From Excel Help > Range_lookup is a logical value that specifies whether you wan

VLOOKUP to find an exact match or an approximate match. If TRUE o
omitted, an approximate match is returned. In other words, if an exac
match is not found, the next largest value that is less tha
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 colum

and doesn't find 8. So it decides to use the value in the 6 ro
because 8 is between 6 and 11. Unless your real data table is set u
differently than your sample table, VLOOKUP would seem to work for you

 

[Dave O]

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=D13),F1:F3)+SUMPRODUCT(--(I1=E1:E3),F1:F3)

 

[lsu-i-like]

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.