Hi there with the help from this forum I was able to create a vlookup with a dynamic search range on the same page (Sheet 1 in the attached example). data is repeated throughout but I only wanna do my vlookup in block "b". I put in 5 as input and the vlookup returns b5 as I wanted. here is the equation I used and worked beautifully:
=VLOOKUP(F1,OFFSET(INDIRECT(ADDRESS(MATCH("b",D1:INDEX(D:D,),0),2)),0,0,6,2),2,FALSE)
However, I need to do the search and return the previous answer (b5) in Sheet 2, how should I do it?
I tried to use the following equation to add "sheet1" into the previous equation in hope to make it search from sheet 2, but failed.
=VLOOKUP(B1,INDIRECT("Sheet1!"&OFFSET(INDIRECT(ADDRESS(MATCH("b",Sheet1!D1:INDEX(Sheet1!D:D,),0),2)),0,0,6,2)),2,FALSE)
Can anyone help? Attached is the worksheet.
kenneth