Ability to reference cell while looking up a value

I am completely stumped on how I can go about my situation...

My spreadsheet requires a way that I can lookup the value in a cell, which is a formula containing text and math, however that cell is additionally looking up a value from another cell. The issue I have is that the values being referenced in the lookup aren't unique, and are across multiple ranges. Essentially I need to be able to reference the cell which is looking up the initial value and offset the returned referenced cell.

To best explain how it work...

• I want to enter a number into a cell, Sheet1!A1

Sheet1!A1 = 40

• I want a lookup function in Sheet1!C1 that returns the value in Sheet2!B1

Sheet1!C1 = INDEX(Table2[@COLUMN2],MATCH(Table1[@COLUMN1],Table2[@COLUMN1],0))

• I want the value from Sheet1!A1 to be in the formula on the cell at Sheet2!B1, however it must be referenced using an offset

Sheet2!B1 = "SOME TEXT"&Sheet1!A1*Sheet2!C1 = "SOME TEXT"&INDIRECT(ADDRESS(ROW(),COLUMN()-2))*Sheet2!C1

The trickiest part of this, is that the ADDRESS function returns a cell reference as a string. As I want Sheet2!B1 to lookup the value of Sheet1!A1, I must enter the formula of Sheet2!B1 as text and evaluate the formula in Sheet1!C1, otherwise ADDRESS(ROW(),COLUMN()) will return the value of Sheet2!A1 and not Sheet1!A1. As mentioned above, the first value, 40→Sheet1!A1 is a range of values in multiple tables and so is the returned cell of Sheet1!C1. As I need to reevaluate the cell of Sheet1!C1, the best method I can find for this is using an Excel macro of EVALUATE in the name manager, which is reevaluated in Sheet1!D1

Sheet1!D1 = EVALUATE(GET.CELL(5,INDIRECT("RC[-1]",FALSE))) = EVALUATE(Sheet1!C1)

This method works as I need, the final issue that I have is because I'm using INDIRECT in the name manager formula, I am unable to use INDIRECT again in Sheet2!B1, I can't work out how to reapply it to the formula, I end up with a string of the cell's address instead of the cell's value (A1 & RC[-2] should be 40, not the address) 

Sheet1!C1 = "SOME TEXT"&ADDRESS(ROW(),COLUMN()-2,4) Sheet1!D1 = SOME TEXT A1

To put it into some actual data, that can imported into excel and is a direct reference to what I want achieved. I'm 100% open to alternatives, suggestions or methods of how to get my spreadsheet to work this way. The below results in #VALUE, as example it's trying to multiply John*1.1 and not 160*1.1. If I use it as a text formula and wrap the ADDRESS in INDIRECT, I get #REF as it's result, however if I omit the INDIRECT it will result in C2*1.1 and again, not 160*1.1 as the address is a string not a cell reference