You have a table that has a long list of data, and you want to get the specific data, try VOOKUP, and see if it fits your need.
The test steps - LibreOffice 7.5.2.2
OS: Windows 11 Home
Download from the developer's website: https://www.libreoffice.org/download/download-libreoffice
1. Column A to D is the table.
First thing is sorting, select columns.
Activate AutoFilter.
Your table must have column heads, it will ask you for using the first row as column heads, click 'OK'.
The first column is the key for VLOOKUP because it will look up a cell in the first column. You can set which is the first column in the formula, the first column depends on your settings, it doesn't have to be the first column of the table.
In my case, I set 'ID' is the first column so I sort column 'ID'in ascending order.
Click the arrow and select sort ascending.
I used a cell E4 for VLOOKUP. Insert VLOOKUP in the target cell.
I think about this line: ask A in the table, what is B?
In the picture below, search criterion is A in the line above.
A is the value in the first column you set in VLOOKUP.
I set search criterion to cell E1.
In the picture below, Array is the table in the link I think about when using VLOOKUP, you can set the table starting with column B, the first column in the formular is column B.
In my case, I select column A to D.
In the picture below, Index is B in my line above.
I set it to 3, 3 is column 3 that is sports.
To complete the line in human speaking is 'I ask A, what is sports?'.
To make it look pretty: I ask the person who has ID 002, what is your favorite sport?
In the picture below, sorted range lookup is the last value you have to set.
On libreofficehelp.com, it says this value is search type.
search_type: 0=Exact match; 1=Approximate match; default is 1 (approx match).
I set it to 0 for exact match.
In the picture below, I set cell E1 to 007.
My line is: I ask the person who has ID 007, what is your favorite sport?
The answer is rope climbing.
If I change 'B' in my line to 4.
My line is: I ask the person who has ID 007, what is your favorite color?
The answer is black.
If I change the table area to column B to D, and change 'B' (in my line) to 3. And set cell E1 to D.
Then I convert to human speaking: I ask the person whose name is D, what is your favorite color?
The answer is red.
2. 'VLOOKUP only returns the first match', that line on libreofficehelp.com. So the first column shouldn't have duplicates.
You can read 'managing the duplicates' in my previous post.
That's all I have for VLOOKUP.
The test steps - LibreOffice 7.5.2.2
OS: Windows 11 Home
Download from the developer's website: https://www.libreoffice.org/download/download-libreoffice
No comments:
Post a Comment