LOOKUP FUNCTIONS

Allow you to search for a specific value in a table or range and return a corresponding value from another part of the table.

VLOOKUP FUNCTION

It is useful when data is organized vertically as it searches vertically.

It searches for a value in the first column of a table and returns a value from a corresponding row in another column.

Necessary whenever you need to retrieve infromation from a colimn to the right of the lookup column.

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

• lookup_value is the value you want to lookup.
• table_array is the range of cells the lookup value and column from which you want to retrieve data
• col_index_num is the column number from 1 from which to retrieve the value
• range_lookup: FALSE for an exact match TRUE for an appropriate match.

Find the salary of employee ID 4004

=VLOOKUP(4004,A2:E878,5,FALSE)

searches for a value in the first row of a table and returns a value from a specified row in the same column

HLOOKUP FUNCTION

Used when one needs to lookup a value in the first row and retrieve data from the rows beneath it.

=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

HLOOKUP searches horizontally left to right across rows.

Used when data is structured in rows.

Get the salary of employee ID 10001

=HLOOKUP(10001,A1:A4,4,FALSE)

XLOOKUP FUNCTION

Works in newer versions of excel.

One can serach both vertically and horizontally.

It is the most advanced and flexible lookup function replacing both horizontal and vertical lookup.

= XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

Get the salary of employee ID 10002

= XLOOKUP(10002, A2:A878, E2:E878, "NOT FOUND")

INDEX AND MATCH FUNCTIONS

INDEX

Returns a value from a specified row and column within a range.

MATCH

Finds the position of a value within a range and returns its relative position.

A combination of both creates more flexible and efficient lookups in comparison to vertical and horizontal lookup.

This functions encompass flexibility as it allows you lookup values in any column.

Efficient for large datasets since

• MATCH : Finds the position

=MATCH(lookup_value,lookup_array,[match_type])

• INDEX : Retrieves the data

=INDEX(array,row_num,[column-num])

Find the salary of bruno

=INDEX(E2:E878, MATCH("Bruno", B2:B878, 0))