QuitExams

Introduction

The Lookup and Reference functions in LibreOffice Calc and Excel are used to search for data within a table or range and return relevant information. These include functions like VLOOKUP, HLOOKUP, INDEX, MATCH, and the newer XLOOKUP.

1️⃣ VLOOKUP (Vertical Lookup)

Purpose: Searches for a value in the first column of a table and returns data from another column in the same row.

Example:

Formula: =VLOOKUP(103;A2:C5;3;0)

Explanation: Looks up 103 in column A and returns value from column 3 (Price) → 7000.

2️⃣ HLOOKUP (Horizontal Lookup)

Purpose: Searches for a value in the top row and returns data from another row in the same column.

Example:

Formula: =HLOOKUP(103;B1:E2;2;0)

Result: 7000

3️⃣ INDEX Function

Purpose: Returns the value of a cell in a specific row and column of a range.

Example:

Formula: =INDEX(C2:C4;2)

Result: 40000

Explanation: Returns 2nd value from the Salary column.

4️⃣ MATCH Function

Purpose: Returns the position of a lookup value in a range.

Example:

Find the position of “Raj” in the Name column.

Formula: =MATCH("Raj";A2:A4;0)

Result: 3

Explanation: “Raj” appears in the 3rd row of the range A2:A4.

5️⃣ Combining INDEX and MATCH

Purpose: Used together, these functions perform flexible lookups (similar to VLOOKUP, but more powerful).

Example:

Get Salary for “Mary”.

Formula: =INDEX(C2:C4;MATCH("Mary";A2:A4;0))

Result: 40000

6️⃣ XLOOKUP (Extended Lookup)

Purpose: A modern replacement for VLOOKUP and HLOOKUP. It searches for a value and returns a corresponding value from another range, supporting both vertical and horizontal lookups.

Example:

Formula: =XLOOKUP("Monitor";A2:A5;B2:B5)

Result: 7000

📊 Summary Table

Conclusion

Lookup and reference functions are essential tools for managing and retrieving data efficiently. INDEX + MATCH and XLOOKUP offer advanced flexibility, making them ideal for modern data analysis in LibreOffice Calc.