Lookup
Function: - The
Microsoft Excel LOOKUP function returns a value from a range (one row or one
column) or from an array.
Syntax: - =LOOKUP (value,
lookup_range, result_range)
Order ID
|
Product
|
Unit Price
|
Quantity
|
10247
|
Apples
|
14.00
|
12
|
10249
|
Oranges
|
9.80
|
10
|
10250
|
Banana
|
34.80
|
5
|
10251
|
Pears
|
18.60
|
9
|
10252
|
Grapes
|
42.30
|
40
|
Ex: - =Lookup (10251,A1:A6,B1:B6)
Result: - Pears
Hlookup Function: - The Microsoft Excel HLOOKUP function performs a horizontal
lookup by searching for a value in the top row of the table and returning the value in the same
column based on the index
number.
Syntax: - =HlOOKUP (value, table,index no, approximate match)
v Value: - The value to search for in the first row of the table.
v Table: - Two or more rows of data that is sorted in ascending order.
v Index number: -The row number in table from
which the matching value must be returned. The first row is 1.
v Approximate match: -Optional. Enter FALSE to find an exact match.
Enter TRUE to find an approximate match. If this parameter is omitted, TRUE is
the default.
Order
ID
|
10247
|
10249
|
10250
|
10251
|
20252
|
10253
|
Unit
Price
|
14.00
|
18.60
|
7.70
|
16.80
|
16.80
|
64.80
|
Quantity
|
12
|
9
|
10
|
6
|
20
|
40
|
Ex: - =Hlookup
(10251,A1:G3,2,False)
Result: - 16.80
Vlookup: - The
VLOOKUP function performs a vertical lookup by searching for a value in the
first column of a table and returning the value in the same
row in the index no. position.
Syntax: - =VLOOKUP ( value, table, index_number, approximate match)
Order ID
|
Product
|
Unit Price
|
Quantity
|
10247
|
Apples
|
14.00
|
12
|
10249
|
Oranges
|
9.80
|
10
|
10250
|
Banana
|
34.80
|
5
|
10251
|
Pears
|
18.60
|
9
|
10252
|
Grapes
|
42.30
|
40
|
Ex:
- =VLOOKUP(10251, A1:B6, 2,
FALSE)
Result : "Pears" Returns value in 2nd column
Ex: - =VLOOKUP(10251,
A1:B6, 2, FALSE)
Result : "18.60" Returns value in 3rd column