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
IF OR FUNCTION: -The IF
function can be combined with the OR function to allow you to test for multiple conditions. But in
this case, only one or more of the conditions within the OR function needs to
be TRUE for the condition to be met.
|
Product
|
Quantity
|
|
Apples
|
10
|
|
Oranges
|
7
|
|
Paper
|
250
|
Ex:
- =IF (OR(A2="Apples",A2="Oranges"),
"Fruit", "Other")
Result: - "Fruit"
Ex:
- =IF (OR(A4="Apples",A4="Oranges"),
"Fruit", "Other")
Result: - "other"
Concatenate:
- It Combine the more cell text into one cell.
Syntax: - =concatenate
(text1,text2, text3)
Ex: - =concatenate (A1,B1,C1)