Multiple data validations based on user input?

Hello! I have a workbook that multiple users enter data into every day (we create a new one each month). The volume is quite large; by the end of the month there are easily over ten thousand entries.

I need the users to stop entering inaccurate data. I'm not sure how to achieve what I'm looking for. Basically I need four special columns, G:J.

Column G was easy enough. I set Data Validation to a list of USA States taken from a reference sheet in the workbook.

I want column H to have a dropdown menu that then shows the corresponding form names associated with the State the user chose in column G ( i.e. user chooses CO, so I want only CO forms to be listed in the dropdown menu). This data is pulled from the reference sheet.

I want column I to not be a user-entry field. I want it to auto-populate with the ID number given to each form in the reference sheet based on what the user put in columns G and H. There are hundreds of different forms and corresponding IDs.

I want column J to be more or less like column I; one with automatically filled cells based on G and H. This will be a one or two word description of the form's purpose, also pulled from my reference sheet. There are only 4 or 5 different descriptions.

All help is appreciated; I'm not sure where to start. Thank you in advance!

This looks great! You make it look so easy. I've taken it and put in the actual form names and information.

I have run into two problems, however.

Columns I:J occasionally return a "#N/A" error. From what I can see, it's only on Form names that start with a number or special character. This may be purely speculation though, since I only put in about 15 test entries. Aside from that, some of the IDs are wrong, as in the same ID is showing up for multiple forms, and some of them are just wrong altogether.

Column J now isn't always giving me consistently accurate information. Brief example: CA Form 1's description in the Forms Data table is "dmv form", but on the Input Sheet, Column J's description for that entry reads "title". My best guess for why this is happening is because some state form names are the same, and the only way to tell the difference is by specifying the state (or by going off of the ID number, which is different for every form no matter the state). Is there a way to have the LOOKUP function look for G2 (state) and H2 (form name) in the Forms Data table to return the proper result?

I tried changing "=LOOKUP(H2,tblFormData[Form],tblFormData[ID]" to "=LOOKUP(G2:H2,tblFormData[Form],tblFormData[ID]" and got a "#VALUE!" error. What am I doing wrong?

Thank you so much for your help, isskint!

I've had brief experience with array formulas, so I'm no expert....but I did try putting that formula you mentioned into column J, both as a normal and array formula, neither worked. I got an error message:

"The formula you typed contains an error.

• For information about fixing common formula problems, click Help.
• To get assistance in entering a function, click Function Wizard (Formulas tab, Function Library group).
• If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark (')."

then highlighted "tblInput[@Form]"....Not sure what's going on with all that.