This may be an easy one (at least conceptually) -- if so, please accept my apologies.
I'm trying to offset from the value that I might normall VLOOKUP on another sheet.
In other words, lets suppose I have two sheets -- data and output. On the output sheet, in cell A1, I have "Apple"; in cell A2, I have "Pear." Now, let's say back on the Data tab "Apple" in in cell A935, and Pear in is cell A337. Here's what I'm trying to do: in cell B1 of the Output tab, create a formula that will return the value from the Data tab that is five rows down, and three rows right of "Apple" -- in other words, cell C940 from the Data tab. In cell B2, I'd like to do something similar, but offsetting from "Pear."
Now, this sounds simple, and a VLOOKUP gets me so close (it can find Apple in A935, and take me right three columns), but how do I get it to look DOWN five rows at the same time? I've tried combining an OFFSET with the VLOOKUP, but that obviously doesn't work because the formula is trying to offset from the value, rather than the cell reference. So, if I could figure out a formula to give me the cell reference of where Apple is on my Data tab (A935), and offset would work perfectly.
Normally, I think I could figure this out, but my Microfost Excel help doesn't work, so I'm just experimenting with formulas like ADDRESS, MATC, OFFSET, and combinations of VLOOKUP and HLOOKUP, etc.
Can anyone think of a good way to do this? THANKS!
I'm trying to offset from the value that I might normall VLOOKUP on another sheet.
In other words, lets suppose I have two sheets -- data and output. On the output sheet, in cell A1, I have "Apple"; in cell A2, I have "Pear." Now, let's say back on the Data tab "Apple" in in cell A935, and Pear in is cell A337. Here's what I'm trying to do: in cell B1 of the Output tab, create a formula that will return the value from the Data tab that is five rows down, and three rows right of "Apple" -- in other words, cell C940 from the Data tab. In cell B2, I'd like to do something similar, but offsetting from "Pear."
Now, this sounds simple, and a VLOOKUP gets me so close (it can find Apple in A935, and take me right three columns), but how do I get it to look DOWN five rows at the same time? I've tried combining an OFFSET with the VLOOKUP, but that obviously doesn't work because the formula is trying to offset from the value, rather than the cell reference. So, if I could figure out a formula to give me the cell reference of where Apple is on my Data tab (A935), and offset would work perfectly.
Normally, I think I could figure this out, but my Microfost Excel help doesn't work, so I'm just experimenting with formulas like ADDRESS, MATC, OFFSET, and combinations of VLOOKUP and HLOOKUP, etc.
Can anyone think of a good way to do this? THANKS!
