Page 262 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Excel Lookup functions
Below is a quick overview of the most popular formulas to perform lookup in Excel, their main advantages and
drawbacks.

LOOKUP function
The LOOKUP function in Excel can perform the simplest types of vertical and horizontal lookups.
Pros: Easy-to-use.
Cons: Limited functionality, cannot work with unsorted data (requires sorting the lookup column/row in ascending
order).
For more information, please see How to use Excel
LOOKUP function.
VLOOKUP function

It’s an improved version of the LOOKUP function specially designed to do vertical lookup in columns.
Pros: Relatively easy to use, can work with exact and approximate match.
Cons: Cannot look at its left, stops working when a column is inserted into or removed from the lookup table, a
lookup value cannot exceed 255 characters, requires much processing power on large datasets.

For more information, please see Excel VLOOKUP tutorial for beginners.
HLOOKUP function
It’s a horizontal counterpart of VLOOKUP that searches for a value in the first row of the lookup table and returns
the value in the same position from another row.
Pros: Easy to use, can return exact and approximate matches.
Cons: Can only search in the topmost row of the lookup table, is affected by the insertion or deletion of rows, a
lookup value should be under 255 characters.

For more information, please see How to use HLOOKUP in Excel.
VLOOKUP MATCH / HLOOKUP MATCH
A dynamic column or row reference created by MATCH makes this Excel lookup formula immune to the changes
made in the dataset. In other words, with some help from MATCH, the VLOOKUP and HLOOKUP functions can
return correct values no matter how many columns/rows have been inserted to or deleted from a lookup table.
Formula for Vertical lookup

VLOOKUP(lookup_value, lookup_table,
MATCH(return_column_name, column_headers, 0), FALSE)
Formula for Horizontal lookup
HLOOKUP(lookup_value, lookup_table,

MATCH(return_row_name, row_headers, 0), FALSE)

249

CITS : IT&ITES - Computer Software Application - Lesson 63 - 77