formula for Multiple cascading dropdown with auto-populate query|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hello,

Each of the data sets and price correlations are generic examples as I cannot use the original company data, so to keep it simple, here is an MS 365 excel sheet with general name association that uses the same formula formats as my internal company documents.

I am looking for assistance in finding the correct formula to assist with auto-populating a set of data depending on the chosen dropdown fields.

GOAL: Auto-populate the corresponding transmission cost data value of a given vehicle by choosing one of the two corresponding origins in drop down #1, then pick from those related Domestic or Foreign options in drop down #2.

• I have attached the generic excel sheet so formulas and tables can be referenced. I have attempted to use HLOOKUP, IF/THEN, AND/OR statements in different combinations but I am missing the link somehow to referencing the cost associated with a chosen car in the second dropdown.
• I have named table associations for the various groups of data
• I color coded the matching data sets to try and make it easier to follow.

Origin – Domestic & Foreign

1st Dropdown code (Cell B2):

=INDIRECT("Origins")

Domestic – Ford, Chevrolet, Chrysler

Foreign – Kia, Toyota, Audi

2nd Dropdown code (Cell C2):

=INDIRECT($B$2)