xlookup to use substituted table numbers

[LIbra4000]

I have an xlookup formula in a master table.  I want to modify the formula to use the table number which I specify in a different column.  I have 28 tables, sequentially numbered 01 to 28.  Ideally my xlookup in the master table, will look at my "table#" column which specifies which table to interrogate.  Can this be done?

The column which specifies the table number does this with a formula which shunts together text and the two digit table number as scraped from another source in another column ="Table"&left([@[othercolumn]],2)

My alternative as I can see it is to nested an IFNA, but I'm hesitant to nest it 28 times... 

[#2]

@LIbra4000 

I'm having a difficult time visualizing what's going on from your description. It sounds like a situation where the INDIRECT function might be the solution (along with XLOOKUP). INDIRECT is good for combining data from cells into a "new address" or new reference. Without seeing your actual workbook--if it doesn't contain proprietary info you could help us help you by posting the actual, or a mockup--anyway, without seeing your actual workbook it's hard for anybody to give other than general ideas.

Try INDIRECT. Here's a link to a good explanation of how it works. https://exceljet.net/excel-functions/excel-indirect-function

[#3]

@mathetes 

Hello, I'm so grateful for your response, I have the faith that you have the start of a solution for me.  Frustratingly I haven't been able to solve based on the help so far.  I'm going to try to explain better.

1. I have a master table

2. I have 28 tabs, each contains a table, each table is a named range

I'm trying to get something like this (below) to work, wanting to use INDIRECT to dynamically update where I have got "Table01":

=XLOOKUP([@[Tab'#]],Table01[Tab'#],Table01[Upload_ID])

what the formula does (this does work)

Looks for Table01, make the match between a unique value contained in identically named columns (Tab'#) in both the master table and Table 01, searches and finds and returns the corresponding value from the "upload_ID" column.

In this case, there are 3 lines in the master table (column Tab'#) which each have a unique value, so 3 values are hunted and returned.

Web research of examples of INDIRECT seem to indicate that =indirect(cellref) would return whatever is at the cell.  However, I get a spill error.  I have tried inserting "@" but only manage to get a ref error.  Because of this, I can't even get started on trying to substitute Table01 with INDIRECT.  

Please let me know what you suggest?

[#4]

@LIbra4000 I concur with @mathetes that INDIRECT should work.  I'm not sure where you are getting the spill error.  Based on the equation you posted here is a guess at what the formula should be like:

=XLOOKUP([@[Tab'#]],
     INDIRECT("Table"&TEXT(Z1,"00")&"[Tab'#]"),
     INDIRECT("Table"&TEXT(Z1,"00")&"[Upload_ID]")
     )

 Where Z1 is the location of the number for the table you need it to query.

Edit: I went back to Original post and see you suggesting: left([@[othercolumn]],2)

I have no idea what is in "othercolumn" but if it is something like "Table#01" then you should be able to replace

TEXT(Z1,"00")       with        RIGHT([@othercolumn],2)

[#5]

@LIbra4000

Let me supplement the answer that @mtarler has given with a question: Why do you have 28 different tabs with essentially identical tables on each?

In general when you do stuff like that--although it may facilitate whatever independent maintenance is needed for each sub-topic (whatever it is that differentiates one from the other)--whenever you do the separating of the raw data into separate tables you are, in a very real way, albeit unintentional,  interfering with Excel's remarkable ability to deal with a complex and complete single database,

• its remarkable ability to produce output reports that deal with a single entity from within that larger single database
• as well as its remarkable ability to produce summary output reports that compare various dimensions of each of those 28 (in your case) sub-entities (the Pivot Table is an example of this comprehensive summary)

Again, without seeing what it is that you're dealing with or knowing the answer to my "Why?" question--to which there may be an entirely legitimate reply and reason to keep it as it is--I'm a big advocate of creating single raw data tables whenever possible, and then using the aforementioned remarkable abilities of Excel to do the extractions I want for subsets as well as across all of the entities. If indeed all of your 28 tables have identical columns (and therefore identical or comparable data within each of those columns), all you'd need to do is add a single column naming or differentiating what is now table01 (for sub-entity 01), and all of the other sub-entities.

• Your XLOOKUP would then be a LOT easier to write.
• And you'd have access to such things as the Pivot Table for cross-tabulated summaries if needed.
• Or to functions like FILTER and other dynamic array functions that could be used to create "dashboard" sheets to do extraction for selected sub-entities.

If you're not aware of these fairly recent dynamic array functions, here's a very useful Microsoft-produced introductory video: https://www.youtube.com/watch?v=9I9DtFOVPIg  See if the functions named and demonstrated in that video could have bearing on your situation.

[#6]

@mathetes 

thanks Mathetes, I get it, and I love pivots usually (though wouldn't help here).  Each table is a discrete report which we receive, the content is exposed credentials reports (each line).  It's necessary to keep them separate in order to visually show traceability back to the original source files and ensure no data integrity issues with data entry.  So you see I'm using Excel for its data handling capabilities and not number crunching on this occasion.   

I've run out of time to take this further today - but I'm looking forward to playing with what you and the other responder has sent me.  (Meanwhile I have done something better with the source for the table reference - more on that another day).  

I really appreciate the help :o)

[#7 · Accepted solution]

@mtarler 

thanks so much for your advice.  I came to a fix in the end - I did some tidying up other stuff to make this as clean as I could.  I need to learn about what the "00" does ;o) and in the meantime I've learned a little about INDIRECT...

XLOOKUP([@[Tab'#]],

INDIRECT(TEXT([@[Table'#]],"00")&"[Tab'#]"),

INDIRECT(TEXT([@[Table'#]],"00")&"[Upload_ID]"))

I'm so happy to have put in some brain work, which will pay dividends in removing tedious data-pasting-grunt-work in the future.  Thanks so much for your part in this - it's my best victory all week :o)

[#8]

@mathetes 

thanks so much for your advice.  I came to a fix in the end.  

I'm so happy to have put in some brain work, which will pay dividends in removing tedious data-pasting-grunt-work in the future.  Thanks so much for your part in this - it's my best victory all week :o)