Getting a cell value 1row down, 4 columns left of a lookup


Posted by Charlie on June 15, 2001 4:43 PM

C5:C8 have number values. D5:P8 hold specific data on each number value in each row C5:C8. (All values C5:P8 come from vlookup formulas directed towards B5) I need to find the cell value for each number in C5:C8 that is down one row and over 5 columns. B5 is NOT an absolute refernce and all values in C5:P8 can change depending on B5's value. Example: Column headings. B=name, C=time of day, D:P=tasks and instructions. "John" is in B5, "Paul" in B6, "Frank" in B7. 1:00am in C5, 2:00am in C6, 3:00am in C7. I want a formula to tell me what Frank is scheduled 1 hour after 1:00am with the data in G6. It seems simple but I have not explained the logic, it would take to long. Any help would be appreciated



Posted by Aladin Akyurek on June 15, 2001 6:03 PM

Try:

=INDIRECT(ADDRESS(MATCH("john",B5:B8,0)+5,7))

Replace "john" in MATCH by a cell ref.

Does it help?

Aladin