Feb 03 2021 04:17 AM
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...
Feb 03 2021 05:39 AM
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
Feb 03 2021 09:07 AM
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?
Feb 03 2021 09:22 AM - edited Feb 03 2021 09:28 AM
@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)
Feb 03 2021 09:42 AM
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,
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.
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.
Feb 03 2021 12:29 PM
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)
Feb 04 2021 02:51 PM
Solutionthanks 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)
Feb 04 2021 02:52 PM
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)
Feb 04 2021 02:51 PM
Solutionthanks 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)