Cell value based on combination of two drop down lists

[tidepooler]

Hello,

I am new to this whole VBA thing and this forum. I hope someone can help me.

I am trying to set up my Excel spreadsheet so that the user can select a text value in a drop down menu, and a four digit year in another drop down menu, and have it display a value based on the text cell and the year cell, in a third cell.

Here's an example:

One drop down list is in the "B" column, and contains the "Fee Year":
2018
2019
2020
2021
2022

The other drop down list is in the "C" column and contains the "Land Use":
Single Family
ADU
Multi-Family
Commercial

As you can see there are many different combinations. The fees in 2018 for Single Family were set in 2018 and should pop up in Column "E" with a lower cost than say, the fees for 2021 and Single Family. I have the cost of each of the fees. Is there a way to use VBA to do a IF/AND type of code to indicate the fee amount in Column "E" for that year and land use type?

TIA.

[oeldere]

Make a table of the data.

After that use VLookup to find those data in your table.




HOW TO ATTACH YOUR SAMPLE WORKBOOK:


Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[tidepooler]

Thank you for replying. I did some research on VLOOKUP for two separate columns, and came up with this formula (I know that B3, C3, and 'Source Data'!A11:C52 are correct - I'm not sure what the '6' is for. The example I saw had a '3' there and I ended up with the same result).

Here is the VLOOKUP function that I used:

[=VLOOKUP(B3&C3,'Source Data'!A11:C52,6)]

The result in the Cell is #N/A. Can someone direct me to where I may be doing this wrong?

Thank you!

[oeldere]

Explanation of your formula:


Lookup B3&C3

In the range 'Source Data'!A11:C52

Give the result of column 6

False or 0 = exact match

True or 1 = not exact match.


You don't have column 6, since A:C is only 3 columns.


This could also give a N/A failure.
Maybe B3&C3 is not found in column A.

[tidepooler]

Thank you for the direction.

So, I changed it to this:

[=VLOOKUP(B3&C3,'Source Data'!A11:C52,3)]

and receive the response as #N/A

If I change it to this (I read somewhere that '*' can be used as an 'AND'):

[=VLOOKUP(B3*C3,'Source Data'!A11:C52,3)]

I receive the response as #VALUE!

Either way, I am not able to obtain the result from my worksheet in the same file. Any other ideas? I'm stumped.

TIA

[oeldere]

I think B3&C3 can't be found in the table (is not available).

Or one is text and the other is value.


Show a small part of the data in an excel file.

HOW TO ATTACH YOUR SAMPLE WORKBOOK:

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[tidepooler]

Hi!

Thanks for responding so quickly. It just so happens that this is already a small file so I will attach it here. The two values are different, in the sense that one is a number, one is text. However, both are indicated as "General". I do not need to do any math on the year, so it could be a text file if that is what is necessary.

P.S. I tried changing everything to 'text' fields, but this did not work either, as you can see form the attachment. TIA!

[oeldere]

Source Data C11 =A11&B11



F3=VLOOKUP(B3&C3,'Source Data'!$C$11:$D$52,2,0)

See the attached file.

[tidepooler]

Well, it took me a few minutes to see what you meant, but now I got it!

Thank you SO MUCH - you have no idea how this will change my life for the better!

Awesome!

[oeldere]

Glad I could help.

Thanks for marking the question solved.

You can add reputation by clicking on the star * add reputation to all members who helped you.