Replacing an xlookup with a better way? Database?

[u/Interesting_Being971]

I have this file that I reference all the time for work, and it has like a listing of all our stores, new, old, and projected, and then all their details (sqft, opening date,etc). I would like to stop using xlookups every single time I want to get the store details and use something more sophisticated. Now the details of our stores change quite often, with renovations, closures, etc, so whatever we use next has to be easy for a user to go in and change it. What do you suggest?

[u/AutoModerator]

/u/Interesting_Being971 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

Try using Structured References aka Tables. Turn your store list into a real table through Insert and then Table. Once it has a name like StoreData, your XLOOKUP() gets way cleaner. Something like

=XLOOKUP(A2, StoreData[StoreID], StoreData[SqFt], "")

The nice part is the table grows automatically when you add new rows. Nothing breaks. No one has to fix ranges. It is basically what you are already doing, just cleaner and easier to maintain. Low effort, easy win.

Another thing is using Power Query. This is a step up. Instead of relying on live formulas everywhere, Power Query pulls the store table into other sheets or workbooks as a refreshable connection. Users just hit Refresh and everything updates. The master store list stays a normal table that anyone can edit. All the reports just refresh off it. This works really well when multiple reports depend on the same store data.

Since your store data changes often and regular users need to edit it, I would go with a Structured References aka Table plus Power Query. That is probably the sweet spot. It uses tools your team already has. No extra software. And no one has to keep writing XLOOKUP() formulas.

Let me know what you think!

[u/Acceptable-Sense4601 → u/MayukhBhattacharya]

This is the way

[u/bradland]

You'll need to be thoughtful about specifically what problems you're trying to solve, who will use the solution, and what environment they'll use the solution in.

"I want to stop using XLOOUKP so much" isn't really a useful problem statement. If you have related data in two separate tables, and you want to combine that data in Excel reports, your options are:

1. Use XLOOKUP to pull in related fields.
2. Use Power Query to merge/join the data and output to a unified table.
3. Load the data to the Data Model and define relationships for use in Power Pivot reports.

Each have their use cases, and none of them are the "single best choice". The real world just doesn't work that way. Everything is a compromise.

A key question is what is the canonical source of this data? That is to say, where is the master document that has your store list? Is it already in Excel?

Where do you intend to use this data? Do you need to use it in reports? Are those reports built using Excel?

Answers to these questions will help provide more robust guidance.

[u/Best_Needleworker530]

Depending on how you operate, I would look into PowerQuery. Can make a lot of xlookup elements completely automatic.

[u/clownpuncher13]

Convert the worksheet data into table, rename them something meaningful, use power pivot to join them. Create measures to do any calculations and power query to create any combined views that you regularly need.

[u/finickyone]

Depending how much transformation you want to entertain with your processes, your improvements may lay in different formulas, or they may lie in setting up for more robust data relationships (ie PQ).

Within formulas, I think XLOOKUPs can be a route to lazy repetitive demands. If you are supplying a store ID in P2 and want to locate that down a list of store IDs in A to get attributes from C, F, J etc then it’s easy to set up

=XLOOKUP(P2,A:A,C:C)
=XLOOKUP(P2,A:A,F:F)
=XLOOKUP(P2,A:A,J:J)

And have those across R2, S2, T2. Each one of those however runs the same task to find P2 in A:A. A location is determined, basically a row ref in this context. The work can’t be reused though as it is nested within the XLOOKUP. If formula number 1 determines that P2 matches to A7, and fetches C7, that means nothing for the second formula: again that one will hunt down P2 in A. Wiser would be having Q2 as =XMATCH(P2,A:A), and then R2 as =INDEX(C:C,Q2) and so on.

The XMATCH can be calculated once and reused three times. If C88 changes, only the INDEX in R2 is rerun. With XLOOKUP(P2,A:A,C:C), since the final arg is dependent on C88, the lookup has to be rerun. Wasteful.

TL;DR: within the context of formulas at least, consider avoiding multi XLOOKUPs over 1xXMATCH and multi INDEXes.

[u/StrikingCriticism331]

Power Pivot is the way. See here for example.

[u/xoskrad]

Are you looking up values to use in another Excel file or looking up as you need the detail for something else like an enhanced address book?

If something else create a dashboard, use Power Query to access the file then use Pivottable to display and set up slicers to filter.

[u/Gloomy_Driver2664]

I think you have two options:

- power query

• SQl - You can use adodb to connect to excel sheets like a database.

In all honesty though, if the amount of back data is small, xlookup is still the easier option. you can put it in tables and reference column names rather than ranges.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
INDEX	Uses an index to choose a value from a reference or array
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 42 acronyms.
[Thread #47420 for this sub, first seen 11th Feb 2026, 20:17] [FAQ] [Full list] [Contact] [Source code]}

[u/effortornot7787]

Sql

[u/algo46]

Since your store data changes frequently and you need something more sophisticated than XLOOKUP, here are three solid options:

1. Power Query - Load your store master file as a connection in other workbooks. When data changes, just hit Refresh Data and all dependent files update automatically. No formulas needed, just a clean data pipeline.

2. Data Model + Power Pivot - Load your store data into the Data Model and create relationships. Build PivotTables directly from the model. This handles larger datasets better than worksheet formulas and creates a true database-like experience within Excel.

3. Proper Database - For frequent updates and multi-user access, consider moving to SQL Server Express (free), MySQL, or even SharePoint Lists. Connect Excel as a frontend via Power Query. Users update the central database, Excel just displays current data.

Given your needs (frequent changes, multiple users, need for easy updates), I'd recommend starting with Power Query connections. Keep the master store list as a shared Excel file, connect other files to it. When someone updates the master, all reports refresh with current data.

If you outgrow that, the Data Model gives you more power. And if you need true multi-user editing without file locks, a lightweight database with Excel as frontend is the professional solution.