The VLOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function.
It performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position. VLOOKUP supports approximate and exact matching, and wildcards (*) for partial matches.
The syntax for the VLOOKUP function in Microsoft Excel is:
VLOOKUP( value, table, index_number, [approximate_match] )
Parameters or Arguments:
value
The value to search for in the first column of the table.
table
Two or more columns of data that is sorted in ascending order.
index_number
The column number in table from which the matching value must be returned. The first column is 1.
approximate_match
Enter FALSE to find an exact match. Enter TRUE to find an approximate match. If this parameter is omitted, TRUE is the default.
Example:
Table in Sheet 1 & Sheet 2:
1. To Return the Item Name and Product Group to Sheet 1:
=VLOOKUP($A2,Sheet2!$B$2:$D$34,2,FALSE)
Parameters or Arguments:
value
The Item Number in Sheet 1 Column A
table
The table from Sheet 2 to match with Item Number, we start from column B to column D
index_number
To return from Sheet 2 Product Group we put 2 as the index number (count start from Sheet 2 Column B to Column C), for Item Name will be index number 3.
approximate_match
Input as FALSE to get the exact match result.
2. To Return the Opening and Closing balance for the item:
=IFNA(VLOOKUP("*"&A2&"*",Sheet1!$A$2:$E$17,5,FALSE),"NOT FOUND")
Parameters or Arguments:
value
If you only have the [Sheet 2] Family Item, then we can use wild card (*) to replace the unknown front or behind characters "*"&A2&"*" to partial match with [Sheet 1] Item Number.
table
The table from Sheet 1 to match with Item Number, we start from column A to column E.
index_number
To return Opening we put 4 as the index number (count start from Sheet 1 Column A to Column D), for Closing will be index number 5.
approximate_match
Input as FALSE to get the exact match result.
*To handle instances where the VLOOKUP function does not find a match and returns the #N/A error, we can use the ISNA, IFERROR and IFNA functions to return "Not Found"