6.4 Reference Functions

Last edited 480 days ago by Makiel [Muh-Keel].

VLOOKUP (Vertical Lookup)

Purpose: Searches for a value in the first column of a table or range and returns a value in the same row from a column you specify.

Usage: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Common Use: Ideal when your comparison values (the values you are searching for) are located in a column to the left of the data you want to retrieve.

Employee Information Retrieval: Imagine you have a table with employee IDs in the first column and corresponding details like names, departments, and salaries in the other columns. You can use VLOOKUP to find and retrieve the name of an employee based on their employee ID.

Example Formula: VLOOKUP("E123", A2:D100, 2, FALSE) would search for employee ID "E123" in the range A2 to A100 and return the name from the second column of the table.

Invoice Matching: If you have a list of invoice numbers and payment details, you can use VLOOKUP to find the amount paid against a specific invoice number.

Example Formula: VLOOKUP(456789, A2:B50, 2, TRUE) would find the nearest match to invoice number 456789 in the range A2 to A50 and return the payment details from the second column.

⁠

HLOOKUP (Horizontal Lookup)

Purpose: Searches for a value in the first row of a table or range and returns a value in the same column from a row you specify.

Usage: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Common Use: Best suited when your comparison values are located in a row at the top of the data you want to retrieve.

Monthly Sales Data: Suppose you have a table where the first row contains the months of the year, and subsequent rows contain sales data for different products. You can use HLOOKUP to find sales data for a specific month.

Example Formula: HLOOKUP("March", A1:M3, 2, FALSE) would look for the month "March" in the first row and return the sales figure from the second row.

Course Grade Retrieval: If you have a dataset where the first row has course codes and the following rows list student grades, you can use HLOOKUP to retrieve a specific student's grade in a particular course.

Example Formula: HLOOKUP("ENG101", A1:F10, 5, FALSE) would find the course "ENG101" in the first row and return the grade from the fifth row.

⁠

VLOOKUP (Vertical Lookup)

HLOOKUP (Horizontal Lookup)

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.