Using Vlookup() (or Hlookup()) in OpenOffice.org Calc spreadsheets, with a Data Validity dropdown list

For all you spreadsheet users: here's something kind of cool.

Let's say that you have a set of data. You have a list of items, and for every item that there is a unique item number, category number, and packaging type. (One row and three unique columns.)

Or you have been getting your home entertainment organized and you have a perfect system for throwing parties: for every main dish there is a specific drink, appetizer, dessert, and game.

Having the data isn't the trick. What the data lets you do is that elsewhere in your spreadsheet, you can type or select the first item from a list, and have one or more of the other associated pieces of data pop into the cells next to it. Select the main dish and you also automatically get the associated drink, appetizer, dessert, and game.  

You use =VLOOKUP() OR =HLOOKUP to do this.

Here's an example. I have this data. There are several columns but here are the first two.

Here's one thing about the data. Be sure to sort it. Sort it by the first column, alphabetically or numerically. Select all the data, choose Data > Sort, and sort as usual.

At another spot in the spreadsheet I can set this up so that when I type "Beans and rice" in cell C19, the formula here.....

will automatically display the right type of drink for beans and rice (that I have set up in the data set).

 

How does the formula work?

($C19;$A$10:$D$15;2)

The first part $C19 (the $ is just an absolute reference) is the cell containing the value that I want to look for in the FIRST column in the data set. In case the type of food such as beans and rice.

The second part is the range of data.

The third part is the column containing the data I want. I type 2 for the drink; 3 if I want to display the column containing appetizer information.

You can keep on going by adding more columns. Use the same formula but set it up so that the last argument (the column) is 3, 4, and 5 respectively.

 

and that's how this looks.

If you're thinking that typing the names of the dishes is a lot of work, especially if instead of six main dishes you had 122 part names or numbers, you're right. Ideally you'd set up a dropdown list.

Click in the cell where you want to display the first piece of data, the main dish. (You probably wouldn't make the lists and VLOOKUP positioned right next to the original data set; I'm just showing them side by side because it's simpler, and it's frankly easier to get screen shots this way. ;> )

1. Choose Data > Validity.

2. From the type list select Cell Range, then type an absolute range (with $row$column format) as shown, around the column of labels.

 

2. Click OK.

Then click the little tiny black handle in the lower right corner of that cell where you made the list, and drag it down to put in a list in other cells too.

Now you can just select something from the list, and all the corresponding info, from the data set, will appear in the cells where you've also put the VLOOKUP formula.

(HLOOKUP is the same, but rotated 90 degrees.)

Comments

[Ray]

what ever happened to the simple drop down list that you can enter in a cell after opening?

[Solveig]

Hi Ray,

Here you go.

http://openoffice.blogs.com/openoffice/2007/07/dropdown-lists.html

[David Sarnowski]

What is the syntax to reference a cell range from a different sheet?

[Steve]

I have a grocery list that has store items in Column C and the corresponding aisle locations in Column E. I have included a data validity function in the C column, so that I can use the drop down menu to choose the items for my grocery list and so I don't have to type in each thing that I want to purchase. Even cooler, is that when I choose something from the drop down list, I have a VLOOKUP function in Column E that tells me what aisle in the store that particular product is located in.
Here is my problem - Every time I open the grocery list spreadsheet up again, the source for my data validity field is gone - That is to say, that it doesn't seem to save. Instead, all I get in the drop down box is a 520 Error code, and for the life of me, I cannot figure out why the spreadsheet won't open up with my data validity fields intact and the way that I left them.
Can anyone help...??

[Snowman]

Hello there
I´m trying to make a dropdownlist. I have made a list in sheet 2 and I´m trying to get info from one row in sheet 2 be selecting a name in colum a in sheet 2 to be listed in sheet 1(with all the info from row 25). The list is long maybe 1-500 or something.
Please help!!!

[Snowman]

Hello there
I´m trying to make a dropdownlist. I have made a list in sheet 2 and I´m trying to get info from one row in sheet 2 be selecting a name in colum a in sheet 2 to be listed in sheet 1(with all the info from row 25). The list is long maybe 1-500 or something.
Please help!!!

[RANGER]

I have been searching for the same thing, tried all kinds of ways to get Validity to work on sheet 3 using data I entered on sheet 1, so far no luck
This is a good program but I wish they had a tutorial on Validity and all
related to it. Any one have a clue? thanks for the help
Ranger

[Nick]

Hello David,

Make sure that you are not saving your file with '.xls' extension.
Save it as '.ods'

Enjoy!