This blog is going to show you how to use different lookup formulas in excel.
VLOOKUP is frequently used to look up data with an Index field. However, this is not the only function in Excel that can be used to look up information. There are other different functions and combinations of functions available for lookup data in excel.
Looking for a value in a table or data range is a common application of Excel. Excel has four specific lookup functions. Aside from that, we can look up a value using a collection of other functions.
In this article, we used the below sales data table to explore all four types of LOOKUP functions.
- How to use VLOOKUP Function in Excel
- How to use HLOOKUP Function in Excel
- How to use LOOKUP Function in Excel
- How to use XLOOKUP Function in Excel
VLOOKUP Function – lookup formulas in Excel
Most excel users everyday use the VLOOKUP function for data processing, it is a well-known LOOKUP function that looks up values from left to right in a table.
The VLOOKUP function returns a value in the same row from a specified column after searching for a value in the first column of a table or array. The function searches for a value in a column.
The Syntax of the VLOOKUP function:
=VLOOKUP ( Value, Array/Table, indexNo, [Boolean_Match])
Parameters
Value: The value you want to search for in the first column of the Table.
Array/Table: Table (Two or more columns) of data, which you want to search.
indexNo: Column number of your table to return the results.
[Boolean_Match]: Optional. FALSE – for an exact match, TRUE – for an approximate match.
If you need to look up a product given in B17 and return the profit of the product for your reports.
Please follow the below steps to use the VLOOKUP function in excel
- Go To cell C17
- Type the below formula
=VLOOKUP (B17, B1:F12, 5, 0)
- Press Enter
VLOOKUP is used exclusively for vertical lookup. Can only lookup values from the right of the lookup value
HLOOKUP Function – lookup formulas in Excel
The HLOOKUP function returns a value in the same column from a specified row after searching for a value in the first row of a table or array. The function searches for a value in a row.
The HLOOKUP function is used in Excel when you wish to look up a value by matching values in columns and getting values from rows. HLOOKUP is the opposite of the VLOOKUP function.
The Syntax of the HLOOKUP function:
=HLOOKUP ( Value, Array/Table, indexNo, [Boolean_Match])
Parameters
Value: The value you want to search for in the first row of the Table.
Array/Table: Table (Two or more columns) of data, which you want to search.
indexNo: Row number of your table to return the results.
[Boolean_Match]: Optional. FALSE – for an exact match, TRUE – for an approximate match.
HLOOKUP is used exclusively for Horizontal lookup. Can only lookup values from the below of the lookup value
If you need to report the Sales values of the product Ipoh Coffee from your data table.
Please follow the below steps to use the HLOOKUP function in excel
- Go To cell C17
- Type the below formula
=VLOOKUP (B17, B1:F12, 5, 0)
- Press Enter
LOOKUP Function – lookup formulas in Excel
The Lookup function in excel looks up a given value in the first column and returns the last column of your data set if you did not give a result array. The LOOKUP function returns the value from the result array If you provide a result array.
The Syntax of the LOOKUP function:
=LOOKUP ( Value, Array, [Result_array])
Parameters
Value: The value you want to search.
Array: Table (Two or more columns) of data, which you want to search.
Result_Array: Optional. One dimensional array to return value. The result return from the Array if you omitted result_array
If you need to find the profit of a specific product. You can use the below LOOKUP formula and get the result.
- Go To cell C17
- Type the below formula
=LOOKUP (B17, B1:F12, F1:F12)
- Press Enter
If you need to report the Sales values of the product Ipoh Coffee from your data table using the LOOKUP formula.
- Go To cell C21
- Type the below formula
=LOOKUP (B21, A1:F1, A5:F5)
- Press Enter
You can use this function for both Vertical and Horizontal lookups in excel.
XLOOKUP Function – lookup formulas in Excel
XLOOKUP function is only available in Office 365 and will not work in an older version, it is one of the most powerful functions in excel.
The XLOOKUP function in excel looks up a given value in a range or an array for a match and returns the corresponding value from a return_array.
The Syntax of the XLOOKUP function:
=XLOOKUP ( Value, Array, Result_array, [if_not_found], [match_mode], [search_mode])
Parameters
Value: The value you want to search.
Array: One-dimensional array you want to search
Result_Array: One-dimensional array to return value.
If_not_found: Optional. Return Value if Value is not found in Array. You can use this parameter to assign a default value if not match.
Match_mode: Optional. 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match
Search_mode: Optional. 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending
If you need to find the profit of a specific product given in B17. You can use the below XLOOKUP formula and get the result.
- Go To cell C17
- Type the below formula
=XLOOKUP (B17, B2:B11, F2:F11, , 0)
- Press Enter
If you need to report the Sales values of the product Ipoh Coffee from your data table using the XLOOKUP formula.
- Go To cell C21
- Type the below formula
=XLOOKUP (B21, A1:F1, A5:F5, , 0)
- Press Enter
This function you can use for both Vertical and Horizontal lookup in excel.
Conclusion
Use VLOOKUP if you need to look up values vertically in your table. HLOOKUP helps you to look up values horizontally.
You can use the LOOKUP function for lookup values both vertically and horizontally.
XLOOKUP is a powerful function and it allows you to give default values if the value is not found.
