Forum Discussion
Solved
Using selections from DropDown lists in Xlookup function/formula
I have an Excel 2021 spreadsheet with multiple tabs (for multiple locations/cities). Each tab has similar data. One column has a list of various fruits/fruit trees and the second column has the quant...
You can achieve this by using the INDIRECT function in combination with your XLOOKUP formula. The INDIRECT function allows you to create a reference to a cell specified by a text string, and you can use this to dynamically refer to the selected tab.
Assuming you have a dropdown list of locations in cell A1 on your summary tab, and your tabs are named after the locations, you can modify your XLOOKUP formula like this:
=XLOOKUP("Apple", INDIRECT("'" & A1 & "'!$A$2:$A$100"), INDIRECT("'" & A1 & "'!$B$2:$B$100"), "Not Found")
Explanation:
- INDIRECT("'" & A1 & "'!$A$2:$A$100"): This creates a reference to the range of fruit names in the selected tab.
- INDIRECT("'" & A1 & "'!$B$2:$B$100"): This creates a reference to the range of quantities in the selected tab.
- XLOOKUP("Apple", ...) searches for "Apple" in the fruit names.
- If "Apple" is found, it returns the corresponding quantity from the quantities range.
- If "Apple" is not found, it returns "Not Found" (you can customize this part).
Adjust the formula according to your actual cell ranges and data structure. This way, when you change the location in the dropdown list (cell A1), the XLOOKUP formula will dynamically refer to the corresponding tab for the data lookup.
Make sure the dropdown list in cell A1 contains the exact names of your tabs so that it matches the tab names. The text was created with the help of AI.
Formularbeginn
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Bill_Miller Unless you have a compelling reason to collect and keep data in separate tabs for each location, I would recommend to combine all in one table and add a column holding the location. Then you can use slicers for the location and the fruit without the need for a dropdown list and the use of INDIRECT.
The attached workbook contains a crude model of what I mean.
- Thanks for your response. I do have a reason for keeping the data from each location on separate tabs so I'm going to pass on your suggestion but I do appreciate you taking the time to respond to me.
