2.3) Lookup and Reference Functions in excel

Lookup and Reference functions in Excel are vital for retrieving specific information from a dataset.

Here are some commonly used and important Lookup and Reference functions:

Lookup Functions:

1) VLOOKUP: Searches for a value in the first column of a table and returns a value in the same row from a specified column.

Example: =VLOOKUP(A2,Data!$A$2:$D$8,2,0) looks up the value in A2 in sheet/cells selected in column and returns the corresponding value from the table selected.

a) Raw data for learning Vlookup in excel

b) Need to find the values by using VLOOKUP formula

c) Finding employee ID details by using vlookup formula

d) In the same way finding department details using vlooup formula

e) In the same way finding salary details using vlooup formula

Note: we can drag down to apply the same formula in the below cells

2) HLOOKUP: Searches for a value in the first row of a table and returns a value in the same column from a specified row.

Example: =HLOOKUP(A1, B1:F5, 3, FALSE) looks up the value in A1 in row 1 and returns the corresponding value from row 3.

3) INDEX: Returns the value of a cell in a specified row and column of a table or range.

Example: =INDEX(A1:C10, 3, 2) returns the value in the third row and second column of the range A1:C10.

4) MATCH: Searches for a value in a range and returns the relative position of the item found.

Example: =MATCH(A1, B1:B10, 0) returns the position of the value in A1 within the range B1:B10.

These Lookup functions are essential for tasks like retrieving specific data points, performing advanced searches, and dynamically referencing cells based on certain criteria. They are widely used in various fields including finance, data analysis, and reporting. Understanding and using these functions effectively can significantly enhance your Excel skills.