Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 5th 20, 06:35 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2020
Posts: 1
Default Trying to replace vlookup with xlookup and not using the indirect function

I have been using this for formula for awhile =VLOOKUP($A2,INDIRECT("'"&B$1&"'!$a$2:$b$12"),2,FA LSE) where Sheet 1 is a summary. 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 but in cell B2 I have 50, in cell, b3 is 0, in cell b4 100.

In essence I want to be able to use xlookup so that it will look at the sheet name (T and GIS) on the summary page and pull the info from the corresponding sheet name (T and GIS).

Thanks in advance

  #2   Report Post  
Old February 5th 20, 11:29 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,138
Default Trying to replace vlookup with xlookup and not using the indirect function

I have been using this for formula for awhile
=VLOOKUP($A2,INDIRECT("'"&B$1&"'!$a$2:$b$12"),2,FA LSE) where Sheet 1 is a
summary. 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 but in cell B2 I
have 50, in cell, b3 is 0, in cell b4 100.

In essence I want to be able to use xlookup so that it will look at the sheet
name (T and GIS) on the summary page and pull the info from the corresponding
sheet name (T and GIS).

Thanks in advance


Since "xlookup" isn't a built-in Excel function I assume it's a custom
user-defined function (UDF). Post the code so we can see what it does now, and
how it can be modified to do what you describe here!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Old February 5th 20, 11:35 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,138
Default Trying to replace vlookup with xlookup and not using the indirect function

Sorry, my bad! I found XLOOKUP() in 365 so I'd have to play with it to figure a
solution for you. Unfortunately, I have too much on my plate right now so
hopefully our resident formula wizard will chime in to save the day!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #4   Report Post  
Old February 16th 20, 12:14 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
xlookup & paste a picture morty Excel Programming 0 March 2nd 10 10:23 AM
Xlookup that returns variable sum data David G. Excel Discussion (Misc queries) 3 November 24th 08 02:38 PM
vlookup with indirect function BNT1 via OfficeKB.com Excel Discussion (Misc queries) 2 March 11th 08 09:34 PM
VLOOKUP from closed file using INDIRECT function Ben Excel Worksheet Functions 3 May 17th 06 01:58 AM
Indirect or Vlookup Function Justin Excel Worksheet Functions 1 July 29th 05 10:38 PM


All times are GMT +1. The time now is 09:04 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017