VLOOKUP with variable table array using INDIRECT and OFFSET

Say I have a conditional format rule in cell C1 that says =VLOOKUP(B1,INDIRECT(A1),2,FALSE)=C1

In cell A1, I have a named range, which changes going down the column... A1 = Atablearray, A2 = Btablearray, so forth

I want to be able to copy the conditional format rule down the column and have the vlookup conditional format formula update with the correct table array for that row.

I can get this to work if my table arrays are fixed, but when I put in a variable defined table array using OFFSET, it doesn't work anymore. (See my example for conditional format rule in cell D1).

Any suggestions?

Thanks!

Tablearray names are changed
AAtablearray as tablearray1
BBtablearray as tablearray2
CCtablearray as tablearray3

Formula for CF in D1

Please Login or Register  to view this content.

Thank you, but this is not what I am looking for.

The range names can be changed as you did, but I need them to stay in Column A and be referenced in the Vlookup formula. This is how my data is set up and that can't change. I also want to be using some type of variable defined range name using OFFSET or something like it, if possible.