VLOOKUP and HLOOKUP
VLOOKUP and HLOOKUP are two functions in Excel that allow
you to search for a value in a table and return a corresponding value from the
same row or column.
The VLOOKUP function searches for a value in the leftmost
column of a table and returns a value in the same row from a column you
specify. Here's the basic syntax:
`=VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])`
- lookup_value: the value you want to search for
- table_array: the table range that contains the lookup
value and the result you want to retrieve
- col_index_num: the column number in the table array from
which you want to retrieve a value
- range_lookup: (optional) specifies whether to perform an
approximate or exact match. If omitted, the default value is TRUE or approximate
match.
The HLOOKUP function is similar to VLOOKUP, but it searches
for a value in the top row of a table and returns a value in the same column
from a row you specify. Here's the basic syntax:
`=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`
- lookup_value: the value you want to search for
- table_array: the table range that contains the lookup
value and the result you want to retrieve
- row_index_num: the row number in the table array from
which you want to retrieve a value
- range_lookup: (optional) specifies whether to perform an
approximate or exact match. If omitted, the default value is TRUE or
approximate match.
For example, suppose you have a table that lists the prices
of different products. You want to find the price of a specific product. You
could use the VLOOKUP function as follows:
`=VLOOKUP("Product A", A1:B10, 2, FALSE)`
This formula will search for "Product A" in the
leftmost column of the table range A1:B10, and return the corresponding price
in the second column.
Similarly, you could use the HLOOKUP function to search for a product in the top row and return the corresponding price in a specific row.
Both VLOOKUP and HLOOKUP are useful functions for retrieving data from a table, but they do have some limitations. For example, they only search for a value in the first row or column of a table. If you need to search for a value in a different column or row, you'll need to use a different formula. Additionally, they may not be the most efficient formulas for very large datasets.