# CONCATENATE as table_array in VLOOKUP

1. ## CONCATENATE as table_array in VLOOKUP

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.  Register To Reply

2. ## Re: CONCATENATE as table_array in VLOOKUP

Try

=VLOOKUP(A3,INDIRECT("Sheet2!\$A\$2:\$B\$"&SUMPRODUCT(MAX((Sheet2!\$A:\$A<>"")*(ROW(Sheet2!A:A))))),2,0)  Register To Reply

3. ## Re: CONCATENATE as table_array in VLOOKUP

Hi John,

That seems to work perfectly!!! Not that I thought of much, but, I certainly did not think to indirectly reference the string combined with "&". Flipping brilliant!!!

Thank You!!!  Register To Reply