Complicated Excel Lookup Formula

[jhardy98]

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 click the drop down arrow, a list appears of options such as these: "PHARMACY, GROCERY STORES, HOME IMPROVEMENT" and so on.

What I would like to do is have some sort of lookup function or IF function that will, when selecting a certain store type in the drop down, fill the cell next to the dropdown cell with criteria pertaining to whichever store type is selected.

For example:

When "PHARMACY" is selected in the drop down, I would like for the cell next to it to read "24 HR, COVID vax, onsite clinic, drive-thru, etc."

When "GROCERY STORES" is selected from the drop down, I would like for the cell next to it to read "Fuel, concept notes, unique offerings (fresh food, delivery, BOPIS, pickup lanes, micro-fulfillment, etc.)"

When "HOME IMPROVEMENT" is selected, I would like for the cell next to it to read "Rental, omni-channel offerings, lumber, garden center, etc."

I have some experience with pivot tables, x and y lookup functions, but I'm not an Excel expert by any means, so it would be really awesome if someone could give me a hand with what I'm trying to do. I hope what I've explained was clear enough but please let me know if more information is needed.

-James

[#2]

@jhardy98 

=INDEX(B1:B3,MATCH(D2,A1:A3,0))

Maybe with this formula as shown in the attached file. You can apply VLOOKUP as well or XLOOKUP if you work with Office365 or 2021. The values in the INDEX and MATCH range A1:B3 are in white font color.

[#3]

@jhardy98 

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.