Scribd
Upload
5 views23 pages

11 Advanced Vlookup Formulas

The document outlines various VLOOKUP techniques in Excel, including basic formulas, error handling with IFERROR, and advanced methods like dynamic VLOOKUP and multiple criteria lookups. Each section provides examples of product sales data and demonstrates how to extract or manipulate this data using VLOOKUP functions. The document serves as a comprehensive guide for users looking to enhance their Excel skills with VLOOKUP functionalities.

Uploaded by

diyayadav4442
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
5 views23 pages

11 Advanced Vlookup Formulas

The document outlines various VLOOKUP techniques in Excel, including basic formulas, error handling with IFERROR, and advanced methods like dynamic VLOOKUP and multiple criteria lookups. Each section provides examples of product sales data and demonstrates how to extract or manipulate this data using VLOOKUP functions. The document serves as a comprehensive guide for users looking to enhance their Excel skills with VLOOKUP functionalities.

Uploaded by

diyayadav4442
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/ 23

1 Basic Vlookup Formula

Product ID Product Region Qty Sold


P01 Mouse East 100
P02 Monitor West 230
P03 Printer South 120
P04 Processor i3 North 144
P05 Processor i4 East 50
P06 Speaker West 340
P07 RGB Light South 210
P08 Monitor North 170
P09 Keyboard East 55
P10 Mouse 3.0 West 77
P11 Wireless Mouse South 154
P12 HDD 256 GB North 230
P13 RAM East 450
P14 ROM West 230
P15 Juicer South 450
Product ID Product
P02 Monitor

Product Qty Sold


Mouse 100

Product ID Product Region Qty Sold


P02 Monitor West 230
2 Vlookup + Iferror - Removing #N/A Error

Product ID Product Region Qty Sold


P01 Mouse East 100
P02 Monitor West 230
P03 Printer South 120
P04 Processor i3 North 144
P05 Processor i4 East 50
P06 Speaker West 340
P07 RGB Light South 210
P08 Monitor North 170
P09 Keyboard East 55
P10 Mouse 3.0 West 77
P11 Wireless Mouse South 154
P12 HDD 256 GB North 230
P13 RAM East 450
P14 ROM West 230
P15 Juicer South 450
Product ID Product
P11
3 Vlookup + Columns Magic

Product ID Product Region Qty Sold


P01 Mouse East 100
P02 Monitor West 230
P03 Printer South 120
P04 Processor i3 North 144
P05 Processor i4 East 50
P06 Speaker West 340
P07 RGB Light South 210
P08 Monitor North 170
P09 Keyboard East 55
P10 Mouse 3.0 West 77
P11 Wireless MouseSouth 154
P12 HDD 256 GB North 230
P13 RAM East 450
P14 ROM West 230
P15 Juicer South 450
Product ID Product Region Qty Sold
P09 Keyboard East 55
4 Dynamic Vlookup - Auto Updated Vlookup Formula With DROPDOWN List

Product ID Product Region Qty Sold


P01 Mouse East 100
P02 Monitor West 230
P03 Printer South 120
P04 Processor i3 North 144
P05 Processor i4 East 50
P06 Speaker West 340
P07 RGB Light South 210
P08 Monitor North 170
P09 Keyboard East 55
P10 Mouse 3.0 West 77
P11 Wireless MouseSouth 154
P12 HDD 256 GB North 230
P13 Abc North 234
th DROPDOWN List

Product ID Qty Sold


P06 340
5 Vlookup To Extract Multiple Records

Product ID Product Region Qty Sold


P01 Mouse East 100
P02 Monitor West 230
P03 Printer South 120
P04 Processor i3 North 144
P05 Processor i4 East 50
P06 Speaker West 340
P07 RGB Light South 210
P08 Monitor North 170
P09 Keyboard East 55
P10 Mouse 3.0 West 77
P11 Wireless MouseSouth 154
P12 HDD 256 GB North 230
Product ID Product Region Qty Sold
P02 Monitor West 230
P05 Processor i4 East 50
P06 Speaker West 340
P09 Keyboard East 55
6 Vlookup With Multiple Criteria ( Multiple Lookup Values )

Helper Product ID Product Region Qty Sold


P01Mouse P01 Mouse East 100
P02Monitor P02 Monitor West 230
P03Printer P03 Printer South 120
P04Processor i3 P04 Processor i3 North 144
P05Processor i4 P05 Processor i4 East 50
P06Speaker P06 Speaker West 340
P07RGB Light P07 RGB Light South 210
P08Monitor P08 Monitor North 170
P09Keyboard P09 Keyboard East 55
P10Mouse 3.0 P10 Mouse 3.0 West 77
P11Wireless MouseP11 Wireless MouseSouth 154
P12HDD 256 GB P12 HDD 256 GB North 230
kup Values )

Product ID Product Qty Sold


P08 Monitor 170
7 Vlookup + Match Formula

Product ID Product Region Qty Sold


P01 Mouse East 100
P02 Monitor West 230
P03 Printer South 120
P04 Processor i3 North 144
P05 Processor i4 East 50
P06 Speaker West 340
P07 RGB Light South 210
P08 Monitor North 170
P09 Keyboard East 55
P10 Mouse 3.0 West 77
P11 Wireless MouseSouth 154
P12 HDD 256 GB North 230
Product ID Product
P08 Monitor

2
8 Vlookup + Choose Formula - Reverse Vlookup

Product ID Product HSN Code Qty Sold


P01 Mouse EP0101 100
P02 Monitor EP0102 230
P03 Printer EP0103 120
P04 Processor i3 EP0104 144
P05 Processor i4 EP0105 50
P06 Speaker EP0106 340
P07 RGB Light EP0107 210
P08 Monitor EP0108 170
P09 Keyboard EP0109 55
P10 Mouse 3.0 EP0110 77
P11 Wireless Mouse EP0111 154
P12 HDD 256 GB EP0112 230
HSN Code product
EP0106 Speaker
9 Vlookup Hack

Product ID Product Jan Feb Mar


P01 Mouse 1200 70000 1200
P02 Monitor 2300 50000 2330
P03 Printer 34000 3400 54000
P04 Processor i3 45000 3400 6500
P05 Processor i4 1200 2300 44500
P06 Speaker 2330 540000 4300
P07 RGB Light 54000 7800 23900
P08 Monitor 6500 3400 3400
P09 Keyboard 44500 2300 3400
P10 Mouse 3.0 4300 5440 2300
P11 Wireless Mouse 23900 8700 540000
P12 HDD 256 GB 23000 23000 2300

Product ID Product Jan Feb Mar


P12 HDD 256 GB 23000 23000 2300
Apr Product ID Jan Feb Mar
2300 P07 54000 7800 23900
540000
7800
3400
2300
5440
8700
54000
6500
44500
4300
44500

Apr
44500
10 Vlookup + Sum Magic

Product ID Product Jan Feb Mar


P01 Mouse 1200 70000 1200
P02 Monitor 2300 50000 2330
P03 Printer 34000 3400 54000
P04 Processor i3 45000 3400 6500
P05 Processor i4 1200 2300 44500
P06 Speaker 2330 540000 4300
P07 RGB Light 54000 7800 23900
P08 Monitor 6500 3400 3400
P09 Keyboard 44500 2300 3400
P10 Mouse 3.0 4300 5440 2300
P11 Wireless Mouse 23900 8700 540000
P12 HDD 256 GB 23000 23000 2300
Apr Product ID Total Sale
2300 P07 94400
540000
7800
3400
2300
5440
8700
54000
6500
44500
4300
44500
11 Vlookup + Trim Formula

Product Jan Feb Mar Apr


Mouse 1200 70000 1200 2300
Monitor 2300 50000 2330 540000
Printer 34000 3400 54000 7800
Processor i3 45000 3400 6500 3400
Processor i4 1200 2300 44500 2300
Speaker 2330 540000 4300 5440
RGB Light 54000 7800 23900 8700
Monitor 2 6500 3400 3400 54000
Keyboard 44500 2300 3400 6500
Mouse 3.0 4300 5440 2300 44500
Wireless Mouse 23900 8700 540000 4300
HDD 256 GB 23000 23000 2300 44500
Product Mar
Speaker 4300
1 Vlookup Basic 7 Vlookup + Match
2 Vlookup + Iferror 8 Vlookup + Choose
3 Vlookup + Columns 9 Vlookup Hack
4 Dynamic Vlookup 10 Vlookup + Sum
5 Vlookup Multiple Records 11 Vlookup + Trim
6 Vlookup Multiple Criteria 12 Free Training File

You might also like