Can Vlookup accomplish this?

[osp]

I changed the range but I'm not sure if I understand this function so
might have messed up something when I edited it.

The range in the MATCH function lookup_array should be from A1 to A3
in the picture I posted right? If that's the case this is the formul
I'm using
=OFFSET([sales.xls]Sheet1!$A$1,MATCH(A2,[sales.xls]Sheet1!$A$1:$A$35,0)+3,4,1,1)

I'm not sure how you got the +3,4,1,1 at the end but Im assuming +3,
is the row and column but not sure why it is +3,4. I figured it woul
be 5,5 in relation to the green colored cells getting to the yello
cells.

I'm going to have to disect this formula and hopefully it will wor
with the spreadsheet at work which is like this but on a much large
scale

 

[Biff]

Hi!

The range in the MATCH function lookup_array should be
from A1 to A35 in the picture I posted right?
Yes.

I'm not sure how you got the +3,4,1,1 at the end but Im
assuming +3,4 is the row and column but not sure why it
is +3,4.

The MATCH function returns the position of the
lookup_value in the lookup_array. For example, the
lookup_value 1000 is in cell A1 of the Sales.xls file and
is in the 1st postion in the lookup_array. So the returned
value is 1. The +3 simply adds 3 to 1. This gives us 4 and
means to offset cell A1 by 4 rows. The 4 in the formula
means to offset cell A1 by 4 columns. So combined, the
formula returns the matched lookup_value offset from cell
A1 by 4 rows and 4 columns.

If the MATCH lookup_value is found in the 20th position,
then the formula returns the value offset from cell A1 by
23 rows and 4 columns.

The 1,1 means return the value of a single cell.

Hope this helps!

Biff

-----Original Message-----

I changed the range but I'm not sure if I understand this function so I
might have messed up something when I edited it.

The range in the MATCH function lookup_array should be from A1 to A35
in the picture I posted right? If that's the case this is the formula
I'm using
=OFFSET([sales.xls]Sheet1!$A$1,MATCH(A2,[sales.xls]Sheet1! $A$1:$A$35,0)+3,4,1,1)

I'm not sure how you got the +3,4,1,1 at the end but Im assuming +3,4
is the row and column but not sure why it is +3,4. I figured it would
be 5,5 in relation to the green colored cells getting to the yellow
cells.

I'm going to have to disect this formula and hopefully it will work
with the spreadsheet at work which is like this but on a much larger
scale.


--
osp
---------------------------------------------------------- --------------
osp's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=14779
View this thread: http://www.excelforum.com/showthread.php?threadid=264009

.