Forum Discussion
Complicated Excel Lookup Formula
Hello all, I'm hoping to get some help on a project I'm working on. In the simplest way put: I have an excel sheet containing a drop down list of types of retailers. For example, when you cli...
I assume that the source of the data validation drop-down is a list of retailers in a column somewhere.
Enter the descriptive text in the column next to it.
Click somewhere in this list, then click Table on the Insert tab of the ribbon. Specify that your table has headers, then click OK.
Excel names the table automatically, but you can change the name if you wish. In the following, I'll use the default name Table1.
Now back to your drop-down. Let's say the first cell with the drop-down is D2. In E2, enter the formula
=XLOOKUP(D2,Table1[Retailer],Table1[Description],"-")If you don't have Microsoft 365 or Excel 2021, use
=IFERROR(VLOOKUP(D2,Table1,2,FALSE),"-")Fill down if required.