How do you do a Vlookup in Mac Numbers?
1. What are the parameters of VLOOKUP function
Vlookup function has 3 parameters
The first parameter is the value you want to search for. In the below example, I want to search for the name of someone who spends 600 a day.
The second parameter is a range of selected cells. The function of this range is a little complex. The first column from the left is where Mac Numbers searches for your requested value in the first parameter. Other columns in the range are the search results.
However, you can only pick one column in the range as a search result. And you tell Mac Numbers the order of that column in the third parameter.
The third parameter is the order of the column from the left chosen to be the search result. In the below example, if the value is 3, Mac Number will display the name. The name column is the third column in the selected range.
The fourth parameter is the accuracy of the match. If you want an exact match and nothing else, you must select exact-match in this parameter. If the lookup function doesn’t find your requested value, it will display an error. And if you don’t write anything, Mac Numbers defaults to the close-match option.
In the below example, I want to search for spending amount around 600, in the table with 3 columns spending earned and name. Then find out the name of the person spending that amount. The formula is as follows.
2. Columns-range vs return-column
Columns-range means a selected area of cells in Mac Numbers. The first column of that area is where Mac Numbers looks for your requested value. In case the look up finds that row with your requested value, it wants to display some result on the screen.
But there are many columns in the found row. Mac Numbers doesn’t know which column to grab the value from. And the return-column was born for this purpose. The return-column is the order of the column in the selected area. In this below example, the name column is the third column from the left in the selected area. Therefore, the return-column value for the name column is 3.
3. Close-match vs Exact-match
The fourth parameter is the accuracy of the search. If you choose the close-match option, and Mac Numbers cannot find the exact match, it will look for a value that is smaller than your search value but the closest to your search value.
If you choose the exact-match option, and Mac Numbers cannot find the exact match, it will display an error message “VLOOKUP couldn’t find the requested value.”. Otherwise, it will display the lookup result.
The VLOOKUP function below displays “Fran” because Fran’s spending is 510 less than 600 in the requested value but the closest to 600 in the selected area.
