Data Searching Tools
VLOOKUP
VLOOKUP is a popular Excel function used to search for a value in the leftmost column of a table and retrieve a corresponding value from a different column. It works vertically (hence the “V” in VLOOKUP). You can specify the lookup value, the table range, the column number containing the desired value, and whether to perform an exact or approximate match.
HLOOKUP
HLOOKUP is similar to VLOOKUP but works horizontally. It searches for a value in the top row of a table and retrieves a corresponding value from a different row. HLOOKUP is useful when you want to search for data across different categories or headers in a row.
PRACTICE TASK 1
PRACTICE TASK 2
PRACTICE TASK 3
PRACTICE TASK 4
PRACTICE TASK 5
XLOOKUP
XLOOKUP is a newer Excel function that provides more flexibility than VLOOKUP and HLOOKUP. It can search for values both vertically and horizontally. XLOOKUP allows you to perform approximate matches, exact matches, and wildcard matches. It is a versatile and powerful function for finding and retrieving data in Excel.
PRACTICE TASK 1
PRACTICE TASK 2
PRACTICE TASK 3
PRACTICE TASK 4
PRACTICE TASK 5
INDEX
XLOOKUP is a newer Excel function that provides more flexibility than VLOOKUP and HLOOKUP. It can search for values both vertically and horizontally. XLOOKUP allows you to perform approximate matches, exact matches, and wildcard matches. It is a versatile and powerful function for finding and retrieving data in Excel.
INDEX With MATCH
INDEX combined with MATCH is a powerful combination for performing advanced lookups in Excel. MATCH helps identify the position of a lookup value within a range, and INDEX retrieves the corresponding value based on the position. This combination is helpful when the lookup column is not the leftmost column, or when you need to perform more complex matching criteria.
PRACTICE TASK 1
PRACTICE TASK 2
PRACTICE TASK 3
PRACTICE TASK 4
PRACTICE TASK 5
INDEX With MATCH With DATA Validation
By combining INDEX with MATCH and data validation, you can create dynamic dropdown lists that allow users to select values from a predefined list. Data validation restricts input to a specified range of values, and INDEX with MATCH retrieves the relevant data based on the user’s selection.
PRACTICE TASK 1
PRACTICE TASK 2
PRACTICE TASK 3
PRACTICE TASK 4
PRACTICE TASK 5