Page 263 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Pros: An improvement over regular Hlookup and Vlookup formulas immune to data insertion or deletion.

Cons: Not very flexible, requires a specific data structure (the lookup value supplied to the MATCH function
should be exactly equal to the name of the return column), cannot work with lookup values exceeding 255
characters.

For more information and formula examples, please see: Excel Vlookup and Match
Excel Hlookup and Match
OFFSET MATCH
A more complex but a more powerful lookup formula, free of many limitations of Vlookup and Hlookup.

Formula for VLookup
OFFSET(lookup_table, MATCH(lookup_value,
OFFSET(lookup_table, 0, n, ROWS(lookup_table), 1) ,0) -1,
m, 1, 1)
Where:

n - is the lookup column offset, i. e. the number of columns to move from the starting point to the lookup column.
m - is the return column offset, i. e. the number of columns to move from the starting point to the return column.
Formula for HLookup
OFFSET(lookup_table, m, MATCH(lookup_value,
OFFSET(lookup_table, n, 0, 1, COLUMNS(lookup_table)), 0) - 1, 1, 1)

Where:
n - is the lookup row offset, i. e. the number of rows to move from the starting point to the lookup row.
m - is the return row offset, i. e. the number of rows to move from the starting point to the return row.
Formula for matrix lookup (by row and column)
Please pay attention that this is an array formula, which is entered by pressing Ctrl + Shift + Enter keys at the
same time.
Pros: Allows performing a left-side Vlookup, an upper Hlookup and two- way lookup (by column and row values),
unaffected by changes in the data set.
Cons: Complex and difficult to remember syntax.
For more information and formula examples, please see: Using OFFSET function in Excel
INDEX MATCH
It’s the best way to do vertical or horizontal lookup in Excel that can replace most of the above formulas. The Index
Match formula is my personal preference and I use it for almost all of my Excel lookups.
Formula for V-Lookup
INDEX (return_column, MATCH (lookup_value, lookup_column, 0))

Formula for H-Lookup
INDEX (return_row, MATCH (lookup_value, lookup_row, 0))
Formula for matrix lookup
An extension of the classic Index Match formula to return a value at the intersection of a specific column and row:

INDEX (lookup_table, MATCH (vertical_lookup_value, lookup_column, 0), MATCH (horizontal_lookup_value,
lookup_row, 0))
Cons: Just one - you need to remember the formula’s syntax. Pros: The most versatile Lookup formula in Excel,
superior to Vlookup, Hlookup and Lookup functions in many respects:
It can do left and upper lookups.

250

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