Using selections from DropDown lists in Xlookup function/formula

[Bill_Miller]

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 quantities of each fruit at that location. There are 4 locations/cities so 4 tabs and a 5th tab for summary purposes that uses the Xlookup function to look at a selected tab to find the correct row for the fruit and pull the quantity. I've created a dropdown list of the 4 locations using the tab names. I want to be able to change dynamically which tab the quantities are pulled from based on which location I select from the dropdown tab.  Can anybody explain to me how to do that? Any help would be appreciated.

[#2 · Accepted solution]

@Bill_Miller 

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:

1. INDIRECT("'" & A1 & "'!$A$2:$A$100"): This creates a reference to the range of fruit names in the selected tab.
2. INDIRECT("'" & A1 & "'!$B$2:$B$100"): This creates a reference to the range of quantities in the selected tab.
3. XLOOKUP("Apple", ...) searches for "Apple" in the fruit names.
4. If "Apple" is found, it returns the corresponding quantity from the quantities range.
5. 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.

[#3]

@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.

[#4]

@NikolinoDEThanks for the suggestion and the guidance on how the syntax should be set up. I apologize for the delay in getting back to you. I'm not familiar with the INDIRECT function so I'll have to give it a try and let you know how it works.  In the meantime, I appreciate your response.

[#5]

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.

[#6]

I apologize again for the delay in getting back to you. I was able to adapt the sample formula you provided and it works perfectly! Thank you very much. Now I have to start learning more about the INDIRECT function!! I liked your response and maked it as the best response as you requested.