VLOOKUP with MATCH and OFFSET

0
votes

In sheet 1 I have below table.

I want to use a formula that fetches the values from sheet 2 (Plan row) once match is found of part number in cell A3 (Sheet 1) and A7 (Sheet 2). Using VLOOKUP with MATCH function for column index to return value from correct week it returns the values from row 7 (Sheet 2). That is values that are in the same row as matched value cell A7 (Sheet 2).

However, I want it to return a value (once match between part number and week is found) from same column but two rows below (row called Plan). I tried using OFFSET function but it does not seem to work with VLOOKUP.

[Sheet 1 ]

Sheet 1

Sheet 2] Sheet 2

1
matchvlookupoffset

1 Answers

0
votes

Answer to my question:

OFFSET and VLOOKUP don’t go together as VLOOKUP returns a value and not an array. Need to change VLOOKUP with INDEX and MATCH and then offset the returned value.

=OFFSET(INDEX('Sheet 2'!$B:$M;MATCH(Sheet 1 $A$3;'Sheet 2'!$A:$A;0);B1);2;0)