VLOOKUP and HLOOKUP Functions in Excel

What is VLOOKUP

VLOOKUP stands for 'Vertical Lookup'. It is an Excel function used to fetch data from one table and return value from another table based on the lookup value. The lookup value is searched vertically in the first column of the table array. VLOOKUP can retrieve data from any worksheet or workbook as long as lookup value exists in both tables.

Term Definition
Lookup value Value searched in the first table
Table array Range containing data table
Column index number Column number in table array from which return value is retrieved

VLOOKUP is useful when data needs to be searched by row.

Limitations of VLOOKUP

The lookup value must be in the first column of the table array. VLOOKUP cannot return value if lookup column is not the leftmost column. This limitation can be overcome by using INDEX and MATCH function combination.

When to Use VLOOKUP

  • Lookup product price from inventory based on product code
  • Find employee details from database based on employee ID
  • Retrieve student scorecard data by searching roll number

What is HLOOKUP

HLOOKUP stands for 'Horizontal Lookup'. It searches for lookup value in the first row of the table array and returns value from specified row. HLOOKUP is used when data needs to be searched horizontally by rows instead of vertically by columns.

Term Definition
Lookup value Searched in first row of table
Table array Range containing data table
Row index number Row number in table array from which return value is retrieved

Limitations of HLOOKUP

The lookup value must be in first row of the table array. HLOOKUP cannot return value if lookup row is not the topmost row. VLOOKUP is more commonly used than HLOOKUP in practice.

Conclusion:

VLOOKUP and HLOOKUP are Excel lookup functions used to search and retrieve data from tables or ranges. VLOOKUP searches vertically down columns while HLOOKUP searches horizontally across rows. Knowing their use cases and limitations is important for effectively applying these functions.

FAQ:

Q1: What is the difference between VLOOKUP and HLOOKUP?

A1: VLOOKUP searches vertically down columns for the lookup value while HLOOKUP searches horizontally across rows for the lookup value.


Q2: When should I use VLOOKUP over HLOOKUP?

A2: VLOOKUP is more commonly used than HLOOKUP. Use VLOOKUP when your data table is organized with categories in columns and data in rows. Use HLOOKUP when data is arranged with categories in rows and data in columns.


Q3: What happens if lookup value is not in first column for VLOOKUP?

A3: VLOOKUP will return #N/A error if the lookup column is not the leftmost column of the table array.


Q4: Is HLOOKUP case-sensitive?

A4: Yes, both VLOOKUP and HLOOKUP are case-sensitive. Lookup value should match the case of data in the table array.


Q5: How can I lookup left or right of the lookup column?

A5: Use INDEX and MATCH combination instead of VLOOKUP to lookup left or right side of the lookup column.


Q6: Can I use wild card characters in lookup value?

A6: No, VLOOKUP and HLOOKUP do not allow use of wild card characters. The lookup value must match exactly.

 

Sarkari Awaaz | Sarkari Job | Sarkari Result

Tags