Forum Discussion

Bill_Miller's avatar
Bill_Miller
Copper Contributor
Nov 16, 2023

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...
  • NikolinoDE's avatar
    Nov 17, 2023

    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.

Resources