Create a list from data between 2 sheets based on text comparaison


I’ve 2 sheets:

  • EQUIPEMENT: One contains data ordered alphabetically (it’s a database): 1 column is the item names, 2nd column is the type (= which is the criteria on which I’d like to perform a comparison)
    image description
  • FORMULAIRE: The other one is a form: 1st column is the type (= the comparison criteria), 2nd is the item names (based on their type) - 3rd is quantity.

From the 2nd column of the form, I’d like to find a list containing the items which belongs to the category selected in the 1st column, referenced in the EQUIPEMENT sheet 2nd column.

I’ve unsuccessfully tried archiving this through DGET:

# Non-working function
  $EQUIPEMENT.$A$1:$B$148;       # From the Database
  $EQUIPEMENT.$B$1:$B$148;       # On the "Category" column
  $A$12:$EQUIPEMENT.$B           # I don't know how to properly formulate a criteria

Could anyone help me to build such a function ?
If my explanations isn’t clear I can provide screenshots or even documents

a list containing the items which belongs…

Do you want to have a drop-down list with item names for the specified type in the FORMULAIRE sheet? Or a complete list of ALL item names for the specified type? Are you talking about a list (register) or a drop-down list (the ability to select one item)?

I’ve added some screenshots. I’d like to have a dropdown list, users must select 1 value from this list

No need DGET(), just create two drop-down lists:

The second formula is hard to see - it says


Of course, 1000 or 10000 is just an example. If you specify the real size of your database, then Calc will work faster

Works like a charm and easy to understand/maintain, thanks a lot ! :slight_smile: