[go: up one dir, main page]
Scribd
Upload
74 views13 pages

Excel V-Lookup and Match Examples

The document contains examples of Excel functions including VLOOKUP, INDEX, MATCH, and lookup tables. It shows how to use VLOOKUP to lookup values based on exact and approximate matches. It also demonstrates using INDEX and MATCH to lookup values and shows examples of lookup tables for shipping charges by region and weight.

Uploaded by

Ruth Pauline Hutapea
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
74 views13 pages

Excel V-Lookup and Match Examples

The document contains examples of Excel functions including VLOOKUP, INDEX, MATCH, and lookup tables. It shows how to use VLOOKUP to lookup values based on exact and approximate matches. It also demonstrates using INDEX and MATCH to lookup values and shows examples of lookup tables for shipping charges by region and weight.

Uploaded by

Ruth Pauline Hutapea
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 13

Basic V-Lookup Example - Exact Match

Stock Code

In Stock

ABC123
ABC127
ABC131
ABC131
ABC135
ABC135
ABC139
BCD001
BCD002
BCD003
BCD004
BCD005
BCD006
BCD007

On Order
58
20
39
53
45
56
50
30
18
18
37
31
49
36

Stock Code
BCD002

In Stock

On Order
18

Basic V-Lookup Example - Approximate Match


Qty Ordered
500
1000
2000
3000
4000

Qty Ordered
750

3
13
15
7
11
12
1
3
6
8
9
10
6
11

Discount Allowed
2%
4%
6%
8%
10%

Discount Allowed
0.02

Cost Per unit Stock Value


6.00
2.00
4.00
5.00
2.00
4.00
4.00
3.00
4.00
5.00
4.00
6.00
5.00
4.00

Stock Value
72

348.00
40.00
156.00
265.00
90.00
224.00
200.00
90.00
72.00
90.00
148.00
186.00
245.00
144.00

Match and Index


Product
Fresh Fruit
Frozen Fruit
Fresh Meat
Frozen Meat
Fresh Vegetables
Frozen Vegetables

January February March


1284
1345
1327
1391
973
1375

Match
March
Fresh Meat

Apples
Oranges
Pears
Bananas
Grapes

Index
4
4

List 1

List 2
Apples
Plums
Oranges
Bananas
Peaches

1226
1116
668
613
1355
1075

881
746
1342
888
1177
1373

Index and Matc

1342

Is in list?
1
0
1
1
0

1342

April
844
1297
1084
1457
686
1272

ndex and Match

May
500
846
878
1281
710
652

June
1257
756
744
1115
628
1036

July
696
629
686
1164
1169
1488

Match and Vlookup


Stock Code

In Stock

ABC123
ABC127
ABC131
ABC131
ABC135
ABC135
ABC139
BCD001
BCD002
BCD003
BCD004
BCD005
BCD006
BCD007

Stock Code
BCD007

58
20
39
53
45
56
50
30
18
18
37
31
49
36

In Stock
0
36

On Order

Stock Value
0

11

On OrderCost PerStock
unit Value
3
13
15
7
11
12
1
3
6
8
9
10
6
11

6.00
2.00
4.00
5.00
2.00
4.00
4.00
3.00
4.00
5.00
4.00
6.00
5.00
4.00

348.00
40.00
156.00
265.00
90.00
224.00
200.00
90.00
72.00
90.00
148.00
186.00
245.00
144.00

Lookup to the Left


Sales Rep

Region Gross Profit

Sarah
Mary
Peter
Emily
Ciaran
Laura
Roan
Ava
Grace

Europe
Asia
USA
Canada
Europe
Asia
USA
Canada
Europe

Gross Profit Sales Rep


7270

Grace

9,267
8,948
8,634
9,953
6,921
6,972
8,322
5,537
7,270

Lookup with text


Stock Code
ABS45TRY
ABC47LKY
GET49YTD

Area

Code
44
45
46
47
48
49

Area
Row
Row
Row
Row
Row
Row

1
2
3
4
5
6

The Excel Club


Choose

Selling Price
Scenario 1
Scenario 2
Scenario 3

100
110
95
Units Sold

Scenario 1
Scenario 2
Scenario 3

700
600
900

Scenario

Revenue Calculation
Selling Price
95
Units
900
Revenue
85,500

Customer
Mark Tolls Ltd
Eastern Ways Ltd
Galways Tops Ltd
Northern Lutes Ltd
Capital Gums Ltd
Hardy Men Ltd
Cleaning Ladies Ltd
Hair Dressers Ltd
Computer Skills Ltd
Core Books Ltd

Customer region Weight KGs Shipping Charge


1
234
15
3
542
35
2
125
5
2
654
17.5
3
456
30
1
765
40
2
432
12.5
1
234
15
3
432
30
1
124
10

Copyright 2014
The Excel Club

Region 1 Shipping charges


Weight KGS Shipping Charge
0
5
100
10
200
15
300
20
400
25
500
30
600
35
700
40
800
40
900
40

Region 2 shipping charges


Weight KGS
Shipping Charge
0
2.5
100
5
200
7.5
300
10
400
12.5
500
15
600
17.5
700
20
800
22.5
900
25

Region 3 shipping charges


Weight KGS Shipping Charge
0
10
100
15
200
20
300
25
400
30
500
35
600
40
700
45
800
50
900
50

You might also like