Order id DISCOUNT TRANSPORTATION

5 0.69 40
10 0.34 38 0.55
15 0.55 15 15
20 0.25 25
25 0.59 40 VLOOKUP(16,B3:D9,3)
30 2.8 10
35 3.55 16 VLOOKUP(2,B3:D9,2)

VLOOKUP(30,B3:D9,3,0)

ORDER NUMBER 35 VLOOKUP(43,B3:D9,2)

DISCOUNT VLOOKUP(D13,B3:D9,2,FALSE)
TRANPORT VLOOKUP(D13,B3:D9,3,FALSE)
VLOOKUP(16,B3:D9,2,1)

VLOOKUP(25,B3:D9,3,TRUE)

#N/A
3.55
VLOOKUP(35,B3:D9,3,0)
Produce Price Count
Apple 0.69 40
Banana 0.34 38 38
Lemon 0.55 15
Orange 0.25 25 INDEX(A2:C6,3,0) Ctrl+shift+enter
Pear 0.59 40
Almond 2.8 10 INDEX(A2:A10,5)
Cashew 3.55 16
Peanut 1.25 20 INDEX(A2:C2,3)
Walnut 1.75 12
216 Walnut

216

INDEX(A2:C10,4,3) Lemon

Lemon
INDEX(A2:C10,2,3)

INDEX((A2:C6,A8:C10),3,1,2) INDEX(A2:A10,6)

INDEX(A2:C2,2)
Lemon 0.55 15

0.55 15

0.55 15
Produce
ID Price Count SALES SALES
5 2.8 10 28 28 3
10 0.69 40 27.6 27.6
15 0.34 38 12.92 12.92 0.25
20 3.55 16 56.8 56.8 #N/A
25 0.55 15 8.25 8.25
30 0.25 25 6.25 6.25
35 1.25 20 25 25 16
40 0.59 40 23.6 23.6
45 1.75 12 21 21
16

ID 35 25 0.25
PRICE PER UNIT 1.25 0.55 16
NO OF UNITS 20 15 56.8
SALES AMOUNT 25 8.25 1
COMMISSION 0.3125 0.045375
PAYMENT 24.6875 8.204625
2 1 3 5 2 1 4 6

10 40 38 16 15 25 20 40

27.6 27.6 27.6 27.6

1
38

16
12
roll numbers
5 MID-1 15 20 10 10 20 4
10 MID-2 19 9 17 3 18 14
15
20 roll number 5 10 15 20 25 30
25 MID-1 15 20 10 10 20 4
30 MID-2 19 9 17 3 18 14
35 TOTAL 34 29 27 13 38 18
40 AVG 17 14.5 13.5 6.5 19 9
45 MIN 15 9 10 3 18 4
MAX 19 20 17 10 20 14

ROLL NUMBER 20
MID-1 10
MID-2 3
TOTAL 13
AVG 6.5
MIN 3
MAX 10
 9 11 9

4 9 13

35 40 45

9 11 9

4 9 13

13 20 22

6.5 10 11

4 9 9
9 11 13
Produce
ID Price Count
5 2.8 40
10 0.69 39
15 0.34 38
20 3.55 25 If 1 then In ascending order
25 0.55 20 If -1 then decending order
30 0.25 16 MATCH(17,A2:A10)
35 1.25 15
40 0.59 12 MATCH(22, C2:C10,-1)
45 1.75 10
MATCH(G11,A14:A22,0) Apples

Produce Price Count

Apples 0.69 40
Bananas 0.34 38 MATCH(17,A2:A10)
Lemons 0.55 15
Oranges 0.25 25 MATCH(22,C2:C10,-1) Apples
Pears 0.59 40
Almonds 2.8 10 MATCH(G11,A14:A22,0) #VALUE!
Cashews 3.55 16 #N/A
Peanuts 1.25 20 #N/A
Walnuts 1.75 12
 1 -1
4 - 7.99 25 - 16.01
8 - 15.99 16 - 8.01
16 - 24.99 8 - 4.01
25 - α 4-α

0.69 40

#N/A #N/A
 1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16

OFFSET(B3:C4,1,1) Ctrl + Shift + Enter

11

6 7
10 11
OFFSET(B3,1,2)

OFFSET(C3,3,1)
32 37 59 73 59
65 55 98 66 87
97 85 57 30 89
84 42 32 84 47
69 41 100 65 95
99 60 87 85 64
73 55 67 48 48
38 37 73 33 64
89 89 81 99 89
77 72 31 35 98
 36 57
95 96
32 53
100 56
43 31
63 98
85 99
39 69
90 88
90 77