View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default Trying to replace vlookup with xlookup and not using the indirect function

George wrote:
....
=VLOOKUP($A2,INDIRECT("'"&B$1&"'!$a$2:$b$12"),2,F ALSE)

... In row 1 there are names which correspond to the sheets. So in B1
I have ATT and I have a sheet named ATT. In C1 I have GIS and I have a
sheet named GIS.

On the summary sheet, in Cell a2 I have January, cell a3 February, a4
March and so on. On sheet T and GIS I have the same information ...


If A2:A13 is January to December in all these workbooks, it doesn't appear you need lookups at all. If the formula above were meant for Sheet1!B2, why not

=INDIRECT("'"&B$1&"'!"&CELL("Address",B2))

? If the ticker symbols in row 1 wouldn't be changing often, you could even use formulas to create formulas.

B2: ="='"&B$1&"'!"&SUBSTITUTE(CELL("Address",B2),"$"," ")

which is a formula which returns text which looks like a formula. Fill this right as far as needed. Then select the entire range of these formulas in row 2, copy, and paste special as values. That converts formulas producing text which looks like formulas to text constants which look like formulas. Then use Find & Replace to replace = with =, yes, replace the equal sign with itself. That effective enters these as formulas. Then fill that entire range down into rows 3 to 13. The idea is to avoid INDIRECT.

If you don't have many of these other worksheets, there's another approach you could take. You have the worksheet names in row 1, I'll assume in B1:M1.. If you have XLOOKUP, you also have TEXTJOIN.

B2: ="=CHOOSE(MATCH(B$1,$B$1:$M$1,0)"&TEXTJOIN(",'",0, INDEX($B$1:$M$1&"'!B2",0))&")"

This is also a formula which results in text which looks like a formula. Copy, paste special as values, then press [F2] then [Enter]. Now fill B2 into B2:M13.

Avoiding INDIRECT and OFFSET isn't necessary when there'd be no more than a few hundred formulas calling them, but it becomes necessary when there could be a few thousand formulas calling them.