Ugly Worksheet - Need Index/Match

[spcalan]

This index/match works perfect:

=INDEX(Plan!AR:AR,MATCH(A:A,Plan!AR:AR,0)+1,1)

I am looking up a sku number in ColA that exists in ColAR, but it's always 1 row below ( hence the +1 ).

Next one that doesn't work for me:
I want to look up same exact sku(ColA) in ColAR, but then look for a different value that is +13 rows and 4 columns to the right.

=INDEX(Plan!AR:BF,MATCH(A:A,Plan!AR:AR,0)+21,4)

Again, the range that has both the lookup value and the answer is AR:BF.
Lookup value is in ColA, +21 rows, and 4 columns to the right

For reference:
CAN-09049 is in cell AR3, and the inventory is in BF24

 

[Scott T]

The column_num of index (where you have 4) is the column of the array column BF where you have the inventory would be the column 15.

Try

=INDEX(Plan!AR:BF,MATCH(A:A,Plan!AR:AR,0)+21,15)

 

[spcalan]

perfect - thanks.

The 15 is like the vlookup column reference. I was thinking I needed to tell the formula how many columns to add.

thanks

 

[spcalan]

of course I am building on this.

Let's say I want to again find the match in AR, but pull the value from Col i instead of the 15th column - this means something to the left of the find.

Example :
SKu is in AR3, and I need to pull the value from i32

=INDEX(Plan!AR:BF,MATCH(A:A,Plan!AR:AR,0)+21,15)

 

[spcalan]

I got it:

=INDEX(Plan!I:AR,MATCH(A:A,Plan!AR:AR,0)+29,1)


thanks !