using a variable named range in a vlookup formula

Hi-

I am trying to figure out how to put a variable into a vlookup formula. The formula below works and gives me the correct response ("Rates_101178" is a named range in my workbook):

=vlookup(b2,Rates_101178,3,false)

BUT, I'd like for the 101178 portion of the named range in the formula above to be variable, based on the contents of cell c2. There are many named ranges in my workbook that start with "Rates_" but each has a different number at the end.

I have tried concatenating the formula, and managed to get the correct formula as text, but I need a VALUE for the formula.

Is there another way to crack this nut?

You can use INDIRECT for that..

=VLOOKUP(B2,INDIRECT("Rates_"&C2),3,FALSE)

That worked like a charm. Thanks. I'll need to learn all about this "indirect" function .

Originally Posted by Daniela1

That worked like a charm. Thanks. I'll need to learn all about this "indirect" function .

Glad to help, thanks for the feedback.