Drop Down List with Specific Values Based on Selection

Question

I am trying to create a kind of booking form that will have values attached to certain selections from a drop down list.

ie. I have a drop down list that includes:

• adult day pass
• adult Half day pass
• child day pass
• child half day pass

And I want each of those selections to have a value attached to it. So when I select 'adult day pass' $60 will appear in the cell next to it, when I select 'adult half day pass' $45 appears in the cell next to it etc.

Does this require complicated formulas/coding?

Thanks

Answer 1

Hi DingbatEri,

Let’s say you have created a drop down list based on values in range A2:A5. When selecting value in the drop down list, you want the corresponding values in range B2:B5 to be automatically populated in a specific cell. For example, when you select Adult Half Day Pass from the drop down, the corresponding price $45 will be populated in E2 as the below screenshot shown. You can use the XLOOKUP function to meet your goal.

Here are the detailed steps:

1. In a new worksheet, type the entries you want to appear in your drop-down list. Then you can quickly convert your list to a table by selecting any cell in the range, and pressing Ctrl+T.

**Notes:**1) Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don't need to do anything else.

2. Now is a good time to Sort data in a range or table in your drop-down list.

2. Select the cell in the worksheet where you want the drop-down list. In my case, I select D2.
3. Go to the Data tab on the Ribbon, then Data Validation.

Note: If you can’t click Data Validation, the worksheet might be protected or shared. Unlock specific areas of a protected workbook or stop sharing the worksheet, and then try step 3 again.

4. On the Settings tab, in the Allow box, click List.
5. Click in the Source box, then select your list range. In my case, I select A2:A5 or input the title of the column directly. If it’s OK for people to leave the cell empty, check the Ignore blank box. Check the In-cell dropdown box. Click OK.

6. Select a blank cell that you want to auto populate the corresponding value. In my case, I select E2.
7. Copy and paste the below formula into it, and then press the Enter key.

=XLOOKUP(D2,A2:A5 ,B2:B5) or =XLOOKUP(D2,Title ,Price)

Note: In the formula, the D2 is the drop down list CELL, A2:A5 is the table range includes the lookup value, and B2:B5 is the table range includes the lookup results. Learn more about XLOOKUP: XLOOKUP function.

8. From now on, when you select a name in the drop down list, E2 will be auto-populated with a specific number. We can manually format E2 as currency.

Best regards,

Madoc

Answer 2

Hi

Please, try this method

1- Create a table anywhere on your workbook like the one in the picture below

2 - Use the formula 

in cell B2 =VLOOKUP(A2,$E$1:$F$5,2,FALSE) and copy/drag down

Change ranges according to your scenario.

Here a video from one of the masters that will give you more details and ideas for your goals.

https://www.youtube.com/watch?v=iJ1MO2xx8wQ&t=305s

Do let us know if you need more help

Regards

Jeovany

Answer 3

Hi DingbatEri,

Have you checked the above replies? Please let us know if you need further assistance.

Best regards,

Madoc

Answer 4

Hi Madoc,

Thank you for your reply! I had a go step by step and the last section where I put the formula in it comes up with "#N/A". Not sure if I'm doing it wrong?

Answer 5

Hi DingbatEri,

Thanks for your update. Per the situation you mentioned, the file may help us better understand and try to fix the issue. Could you prepare and upload an example file you mentioned with no confidential/ sensitive data to OneDrive, then share the link to me via PM? I'd like to have a test from my side.

Best regards,

Madoc