This tutorial will explain how to create a dependent dropdown list in LibreOffice Calc.
The earlier article explains a single dropdown list with data populated from a range inside the sheet. If you are new to dropdown lists data validation, you may read this first.
Table of Contents
Data Setup
Say we have two lists containing values of their respective categories like this.
- Colors
Orange
Blue
Black
White
Green - Planets
Pluto
Mars
Earth
Venus
Jupiter
We will have the first dropdown, which lists the categories, i.e. Colors and Planets.
Based on what the user chooses in the first dropdown, we want to fill the second dropdown with the list of items of the respective chosen dropdown.
I.e. If you choose Planets, the second dropdown would show a list of planets instead of a list of colours and vice versa.
Steps for Data Validation Using Double Dependent Dropdown List
- Select the categories, i.e. A1 and B1. Then from the menu, select
Sheet > Named ranges and expressions > Define.
- Put name as category1. Click Add. You can put any meaningful name you want.
- Likewise, select A2 to B6 and add the named range as category2.
- Select E1, and from the menu select
Data -> Validity
.
In the validity window, chooseAllow=Cell range
and putcategory1
as Source. Press OK.
- Select E2, and from the menu select
Data -> Validity
.
In the validity window, chooseAllow=Cell range
and put below formula as source, then press OK.
INDEX(category2,,MATCH(E1,category1,0))
- Now, click on the small dropdown in E1 cell, you can see category1 i.e. colors and planets would be listed from A1:B1 cells.
- Select Planets.
- Now click E2 dropdown; you can see only planets values are loaded in this dropdown depending on the value of E1.
- Go back to E1 dropdown and select Colors. And likewise, open up the E2 dropdown. You can see it contains the color values dependent on the E1 value.
So this is how you can create a dependent dropdown in LibreOffice Calc.
Explanation of INDEX and MATCH in the second validation
INDEX(category2,,MATCH(E1,category1,0))
MATCH: It returns the relative position of the value selected in E1 after searching in category1 range (A1:B1). The last argument, 0 – denotes it would look for an exact match.
INDEX: returns the subset of an nXn
range from reference category2 based on row and column number. The second argument is the row (which is omitted here as values are present in columns), and the third argument is the column number.
Learn more about Index and Match in this tutorial.
Watch this tutorial on Video
I have made the following video after many questions on Error 501 with the above instructions. The instructions are correct. Follow the steps exactly mentioned above.
Still, have questions? Drop a comment below.

Doesn’t work for me. Keep getting an error 501…
The article is correct. Hence to demonstrate I have made a video here:
https://youtu.be/HBK1FzmQPHc
If you are getting 501, then you must be doing some step incorrectly. Refer the video.
Same here, Error 501 on the second drop down.
The article is correct. Hence to demonstrate I have made a video here:
https://youtu.be/HBK1FzmQPHc
If you are getting 501, then you must be doing some step incorrectly. Refer the video.
I deeply respect and admire the genious minds behind this and the bravery of “loosing” time to help people in trouble with doubts or problems. Thank you people of Excellence !
Causes a error 501, not able to find out why. Please respond
The article is correct. Hence to demonstrate I have made a video here:
https://youtu.be/HBK1FzmQPHc
If you are getting 501, then you must be doing some step incorrectly. Refer the video.
Thank you very much for your great explanations!
I have a question concerning my use-case of this method.
I’m working on a databank with several categories and sub-categories, therefore i need to have this dependent drop-down-lists in every cell of the sub-category column.
The first step works perfect, but of course when inserting the INDEX and MATCH formula, I want the subcategory cell to match the category cell in the same row. I’m looking for a possibility to define this for the whole column, since the loads of data make it impossible to do it manually. Does anyone have an idea how to do this?
Thank you loads in advance!
Wow, Is working perfectly! I was looking long time for this explanations.
Thank you so much!
I did exactly as you do in this tutorial but I also keep getting error 501 in the second drop down menu. No matter what I do, the error still resists. I noticed that the last comma in the formula of the second drop down between the 1 and the 0 automatically disappears after closing the validity window. Very very strange. Does anyone have a solution?
Can you share a screenshot of the formula entry for the second dropdown?
How do you do it when category 2 is a drop down list? Mine isn’t just a manual list like the one above, it is a drop down list where I can choose different options from a column but nothing comes up even when I’ve clicked a few for the drop down selections.
use the source list of the existing dropdown for category2. You may need to use a separate list referring to that source