Forum Discussion
Solved
Controlling XLookup with Dropdown
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 dro...
- 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().
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.
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. 🙂