Controlling XLookup with Dropdown

[Mikethevine]

Hello,

I'm a little new to excel, so go easy on me - I don't know all of the jargon just yet but I'm learning fast. :)

I am trying to allow a user of a workbook to select an option from a dropdown list and their selection alters what my XLookup is referencing as a lookup array in a pivot table on another sheet.

So if my dropdown list is H1 - I'm working on a code like...

=IFERROR(ABS(XLOOKUP(A3,'SALES Pivot'!A:A,'SALES Pivot'!$H$1)),"")

The issue is... $H$1 (the drop down) is on the page that the XLOOKUP is operating in.

Any ideas? I originally had an XLOOKUP that was searching 2 lookup_values and avoiding using a pivot table... but I am running a few hundred XLOOKUPS on each sheet and it was making the workbook very slow... I was hoping that simplifying the xlookup by using a pivot table would help... but it has broken my drop down list toggle. That worked well with this:

=IFERROR(ABS(XLOOKUP(A5&$H$1,FullDATA!A:A&FullDATA!B:B,FullDATA!E:E)),"")

I'll note that I have multiple sheets referencing this same Pivot Table.

Thanks in advance! :)

[#2 · Accepted solution]

so a few things. you are right that you can't just call out H1 as part of that cell reference like that. You could, but I recommend NOT, use the INDIRECT() function to 'paste' together the refence you need. In fact I'm not even going to write that formula out because I recommend against it.
What I will recommend is going back to your original formula EXCEPT use Table References. So step 1 is to go to the FullDATA sheet and highlight the data area (actually just selecting any cell in the area is usually enough for Excel to figure out the table boundaries) and select under HOME->Format as a Table . The pop-up will confirm the area of the data table and if the table has a header row. Then select how you want it to look (doesn't really matter). Once the Table is formatted there should be a Table menu item and in the ribbon for the Table all the way on the left you can see it is probably named Table1. Change that to something more meaningful like myData. Then in the Formula you will replace FullData!A:A with something like myData[FirstName]. In this way Excel will only look at your rows of data in the table (and the table will automatically grow/shrink as you add/remove data) and not do evaluations on the entire column.
The second option is to use GETPIVOTDATA() function but that function is a bit harder to explain without having a file and sample data, but there are plenty of youtube videos too.
Next I want to ask/question why you have hundreds of XLOOKUPS on the sheet and if you might benefit from other functions instead like FILTER().

[#3]

BTW, I kinda jumped right in there so let me step back. Hi and welcome and don't worry about being new, we are just here to try and help.
I know new members can't attach their files but maybe you could tell us more about what you are doing because as I was hinting above there are lots of ways to do things in excel and some can be much easier than others. Also what version of Excel do you have? I mentioned the FILTER() function which is new with Excel 2021 (I believe) and certainly in Excel 365. If you have a sample of your workbook that doesn't have confidential/private/personal info you could also share it using SharePoint or DropBox or something similar.

[#4]

Thanks for the detailed response and the kind words. I am using Excel 365. 

So I am creating a workbook for 7 stores to use and all 7 stores have different data sets. The data dump that I am forced to work with has columns of Item Number, Store Number, Retail Price, Units Sold, and Quantity On Hand. It is about 21,000 rows long. It is important to note that the Item Number is duplicated across all 7 stores... hence why I need to xlookup by both Item Number and Store Number.

So my XLookup is referencing the data dump table by Item Number and Store Number, and returning either retail price, quantity on hand, and units sold last month.

The book has about 15 sheets(broken up by distributor of the items in question), with anywhere from 30 to 500 Item Numbers on each sheet. So in the rows, each item number will have 3 xlookups - returning Price, QOH, and Sales.

More or less, I am creating a tool to help stores order. So they will select their store number from a drop down on each sheet and it will pull the respective data from the data set and drop it into the sheet.

I'm really trying to increase the performance of the sheet so it loads quickly and doesn't snag. I did what you suggested on one of the rows and it works flawlessly.

Let me know if you have any other ideas - but this is definitely an improvement!

[#5]

Also! It is important to note that I have to copy and paste this data into the workbook once a week and upload it to a sharepoint portal for teams to use. Which is why I thought a pivot table would *maybe* remove one or two steps of making the table, naming, and removing unnecessary columns. I could copy and paste and move on with my life. LOL

[#6]

Actually I think a pivot table may be exactly what you want. You can add Slicers (or regular filters) to filter the table based on store, item numbers or what ever else you need. Without seeing and knowing exactly what you have and what you need it is hard but from the sounds of it, I think you are on the right track with the pivot table. If you really can't get that to work I would then recommend looking at that FILTER() function so something like FILTER( myDATA, (myDATA[store]="store 1")*(myDATA[item#]>100)*(myDATA[item#]<300),"none found") will return all the data from store 1 and item#s between 100 and 300.

[#7]

@mtarler 

I think this is 100% the only option (pivot tables) - as my table changes in size slightly from week to week causing the paste to execute VERY VERY slowly. Something about the AutoExpand feature not allowing a table to flex properly. 

I'm going to try this again with pivot tables and hopefully I can reference columns in the pivot table much like I can with a regular table. That fix made my workbook VERY fast - here is to the pivot table method maintaining that speed. :)

[#8]

not sure why the autoexpand of the table isn't working for you, might be something to do with how you are pasting.
I would think pivot table would have same issue in that a pivot table pulls from a table and if the table isn't autoexpanding ...
Finally you can't reference pivot table columns/data the same as a table but as i noted I think a better way for you to go is to add a slicer on the pivot table page instead of using dropdowns and recreating the data/table.