Help with multiple Ranges

[NetWave128]

I need to look up a value (match a value) and return the value in the
row directly above it. The problem is that the value could be in 1 of
3 possible rows...

The return values are times and the lookup values are standard
numbers...

please help...my current equation is as follows

=IF(T8=K52,LOOKUP(K52,T8,T7),IF(K52=D19:T19,LOOKUP
(K52,D19:T19,D18:T18),IF(K52=D30:E30,LOOKUP(K52,D3
0:E30,D29:E29),Not Found)))


Returns #Value

 

[gocush]

Does this help:

=IF(K52=T8,LOOKUP(K52,T8,T7),IF(K52=MyCell,LOOKUP(K52,D14:T14,D13:T13),IF(K52="YYY",LOOKUP(K52,D30:E30,D29:E29),"Not
Found")))

In the above formula you are "looking up" the VALUE in K52.
If it equals the value in T8, your result will be whatever is in T7.

If that does not work the formula looks to see if K52 equals the value
in the cell named MyCell.
(Here you can enter text in quotes, or a SINGLE CELL-G22, or the name
of a cell, or variable, but not a multiple range as you had.)
If this condition is met then the formula looks to match K52 in the
range D14 thru T14 and returns a match from D13:T13, if found.

If this still does not result in a match it checks to see if K52 equals
"YYY". If this is true it searches D30 to E30 for "YYY" and returns a
match from D29-E29,if found.

If none of the above are found, the result is the TEXT (must be in
quotes) "Not Found"

ONE PROBLEM with the above is that the values in your lookup ranges
(D14:T14, and D29:E29) must be in ASCENDING ORDER.

An alternative to this problem is to substitute a combination of INDEX
and MATCH for the LOOKUP function:
You can change the names of the ranges in the following:

=INDEX(ResultRange,MATCH(LookupValue,Book1!LookupList,FALSE))

HTH

Paul

 

[NetWave128]

Thank You very much the equation ended up

=OFFSET(INDEX($D$8:$S$8,MATCH($H$52,$D$8:$S$8,0)),-1,0)


I could not have done it without your help...Thanks again ...

-k