ExcelBanter - Vlookup

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Vlookup (https://www.excelbanter.com/excel-worksheet-functions/186261-vlookup.html)

I am utilizing a vlookup function for retrieving data from multiple
worksheets. I had hoped that I could concatenate a range name and utilize
that as the table array value in the Vlookup function, but I get a "#NA"
value back. I did the concatenate function in another cell and it properly
returned the name of the range name that defines my data range, but either
utilizing the concatenate function in Excel or referencing the cell in the
vlookup function produces an error. Can someone give me an idea.

=CONCATENATE("SAPP",RIGHT(C1,2)) - this returns "SAPP24" - I would like this
to return anything between SAPP24 to SAPP31.

When I insert this value into the below Vlookup function:

=VLOOKUP(A25,B24,6) and run the error analysis, the first value (A25) in the
function returns the proper text, but I get the error when it tries to refer
to cell B24 which is the "Concatentate" function listed above.

Any help would be greatly appreciated.
--
Thanks,
SMH

As long as "SAPP24" is in the same workbook:

=VLOOKUP(A25,INDIRECT(B24),6)

--
Biff
Microsoft Excel MVP

"keydet1979" wrote in message
...
I am utilizing a vlookup function for retrieving data from multiple
worksheets. I had hoped that I could concatenate a range name and utilize
that as the table array value in the Vlookup function, but I get a "#NA"
value back. I did the concatenate function in another cell and it properly
returned the name of the range name that defines my data range, but either
utilizing the concatenate function in Excel or referencing the cell in the
vlookup function produces an error. Can someone give me an idea.

=CONCATENATE("SAPP",RIGHT(C1,2)) - this returns "SAPP24" - I would like
this
to return anything between SAPP24 to SAPP31.

When I insert this value into the below Vlookup function:

=VLOOKUP(A25,B24,6) and run the error analysis, the first value (A25) in
the
function returns the proper text, but I get the error when it tries to
refer
to cell B24 which is the "Concatentate" function listed above.

Any help would be greatly appreciated.
--
Thanks,
SMH