Using Address to get content in a cell

[excelquest41]

I'm trying to find the contents in a specific address in a separate tab.

Essentially if there was a way to use vlookup but return the row above what a typical vlookup would return. Here is what my typical Vlookup looks like for this equation: =VLOOKUP(CONCATENATE(C6,B11),Sheet1!C:E,3,FALSE)

I have gotten to the point where I can return the specific address or row number text but from there I need to be able to make that actually pull the number that is in that field. Here is my address and match formula: =ADDRESS((MATCH(CONCATENATE($C$7,B11),Sheet1!C1:C476,0)-1),5,1,1) I know I can add "sheet1!" and but it still just returns the text of the address i.e. Sheet1!$E$56 when I need it to return the value in that cell.

Please help. If you don't understand my question please let me know and I can better explain. Any help would be greatly appreciated!

Thanks

[NBVC]

Use:

=INDEX(Sheet1!E:E,MATCH(CONCATENATE(C6,B11),Sheet1!C:C,0)-1))

[excelquest41]

Thank you so much it worked perfectly! You are my hero!