To identify the last row with data in the table on Sheet2 I have this formula in cell D1 of Sheet1 as a reference:
=SUMPRODUCT(MAX((Sheet2!$A:$A<>"")*(ROW(Sheet2!A:A))))
I thought to add the above formula to a CONCATENATE formula to be indirectly reference in the VLOOKUP formula in cell B2 of Sheet1, like this:
=CONCATENATE("Sheet2!$A$2:$B$",SUMPRODUCT(MAX((Sheet2!$A:$A<>"")*(ROW(Sheet2!A:A)))))
This VLOOKUP formula, in cell B2, with the INDIRECT reference, works fine:
=VLOOKUP(A2,INDIRECT($D$2),2,FALSE)
!!!BUT!!! When I try to combine the formulas, it does not work, see cell B3:
=VLOOKUP(A3,CONCATENATE("Sheet2!$A$2:$B$",SUMPRODUCT(MAX((Sheet2!$A:$A<>"")*(ROW(Sheet2!A:A))))),2,FALSE)
How can I get the information in cell D2 (CONCATENATE formula) to work as the table_array in the VLOOKUP formula?
*I reviewed the thread with a very similar title but I did not see how it related to my specific issue.
Bookmarks