"Peo Sjoblom" wrote...
=VLOOKUP(A2,INDIRECT(A1),1,0)

note that it is volatile
....

It take some work, but not a lot, for a nonvolatile work-alike. If there
were 29 or fewer ranges that would appear in A1, try a variation on

=VLOOKUP(A2,CHOOSE(MATCH(A1,{"G7:G12","G15:G20","H 7:H12","H15:H20"},0),
G7:G12,G15:G20,H7:H12,H15:H20),1,0)

This could bump into the limit on formula length.