Index/Match

[Subham02]

I made a vlookup formula to indirectly get value from another sheet. The problem is the value i need is below two rows. I know i can't use offset in vlookup and i am not familiar with index/match. Can anyone help. The formula is =VLOOKUP($C$1,INDIRECT("'"&$B3&"'!A:AZ"),2,0) . Remember i need the value of the cell two rows below. Thanks in advance

 

[Aladin Akyurek]

=INDEX(INDIRECT("'"&$B3&"'!A:AZ"),MATCH($C$1,INDIRECT("'"&$B3&"'!A:A"),0)+2,2)

 

[spinfuzer]

Use Choose to combine the ranges.

=VLOOKUP($C$1,CHOOSE({1,2},Column with look up value,all other columns, but pick a range 2 rows below),2,0)

 

[spinfuzer]

Use Choose to combine the ranges.

=VLOOKUP($C$1,CHOOSE({1,2},Column with look up value,all other columns, but pick a range 2 rows below),2,0)

You have to be using a defined number of rows instead of picking the whole column if you use this. Also, you can use OFFSET in the formula.

=VLOOKUP($C$1,CHOOSE({1,2},INDIRECT("'"&$B3&"'!A1:A1000"),OFFSET(INDIRECT("'"&$B3&"'!B1:AZ1000"),2,0),2,0)