Hm.
If I understand correctly (and it's possible that I don't!):
First, I put some test data in A1:B20--this formula actually:
=cell("address",a1)
so each cell contained its address.
I put this in another cell:
=VLOOKUP(C1,A1:B20,2,FALSE)
Then I highlighted A1:B20 (in the formula--not the worksheet)
and hit f9.
My formula changed to:
=VLOOKUP(C1,{"$A$1","$B$1";"$A$2","$B$2";"$A$3","$B$3";"$A$4","$B$4";
"$A$5","$B$5";"$A$6","$B$6";"$A$7","$B$7";"$A$8","$B$8";"$A$9","$B$9";
"$A$10","$B$10";"$A$11","$B$11";"$A$12","$B$12";"$A$13","$B$13";
"$A$14","$B$14";"$A$15","$B$15";"$A$16","$B$16";"$A$17","$B$17";
"$A$18","$B$18";"$A$19","$B$19";"$A$20","$B$20"},2,FALSE)
(all one cell.)
I could also just put
=A1:B20
in a cell. Hit F2 (to edit it) and F9 to convert it to a value:
I got this back with the same test data:
={"$A$1","$B$1";"$A$2","$B$2";"$A$3","$B$3";"$A$4","$B$4";"$A$5","$B$5";
"$A$6","$B$6";"$A$7","$B$7";"$A$8","$B$8";"$A$9","$B$9";"$A$10","$B$10";
"$A$11","$B$11";"$A$12","$B$12";"$A$13","$B$13";"$A$14","$B$14";
"$A$15","$B$15";"$A$16","$B$16";"$A$17","$B$17";"$A$18","$B$18";
"$A$19","$B$19";"$A$20","$B$20"}
But be careful. You can make the formula too long. Excel has a limit of 1024
characters (measured in R1C1 reference style) for any formula.
