2-D Lookup - VLOOKUP w. MATCH - Common Mistake #2

Advanced Excel Crash Course Section 11: Lookup and Conditional Calculations
2 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.72
List Price:  €129.81
You save:  €37.09
£79.26
List Price:  £110.97
You save:  £31.71
CA$135.49
List Price:  CA$189.69
You save:  CA$54.20
A$153.49
List Price:  A$214.89
You save:  A$61.40
S$134.92
List Price:  S$188.90
You save:  S$53.97
HK$782.50
List Price:  HK$1,095.53
You save:  HK$313.03
CHF 90.16
List Price:  CHF 126.23
You save:  CHF 36.07
NOK kr1,085.06
List Price:  NOK kr1,519.13
You save:  NOK kr434.06
DKK kr691.90
List Price:  DKK kr968.69
You save:  DKK kr276.78
NZ$167.41
List Price:  NZ$234.39
You save:  NZ$66.97
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳10,980.73
List Price:  ৳15,373.46
You save:  ৳4,392.73
₹8,335.04
List Price:  ₹11,669.39
You save:  ₹3,334.35
RM472.40
List Price:  RM661.38
You save:  RM188.98
₦139,532.04
List Price:  ₦195,350.44
You save:  ₦55,818.40
₨27,813.53
List Price:  ₨38,940.06
You save:  ₨11,126.52
฿3,638.31
List Price:  ฿5,093.78
You save:  ฿1,455.47
₺3,237.16
List Price:  ₺4,532.16
You save:  ₺1,294.99
B$501.53
List Price:  B$702.17
You save:  B$200.63
R1,888.66
List Price:  R2,644.21
You save:  R755.54
Лв180.76
List Price:  Лв253.07
You save:  Лв72.31
₩134,741.70
List Price:  ₩188,643.77
You save:  ₩53,902.07
₪367.71
List Price:  ₪514.81
You save:  ₪147.10
₱5,617.33
List Price:  ₱7,864.49
You save:  ₱2,247.16
¥15,132.45
List Price:  ¥21,186.03
You save:  ¥6,053.58
MX$1,658.17
List Price:  MX$2,321.51
You save:  MX$663.33
QR364.88
List Price:  QR510.84
You save:  QR145.96
P1,378.06
List Price:  P1,929.34
You save:  P551.28
KSh13,256.67
List Price:  KSh18,559.87
You save:  KSh5,303.20
E£4,744.14
List Price:  E£6,641.99
You save:  E£1,897.84
ብር5,687.29
List Price:  ብር7,962.44
You save:  ብር2,275.14
Kz83,433.85
List Price:  Kz116,810.73
You save:  Kz33,376.88
CLP$98,290.17
List Price:  CLP$137,610.17
You save:  CLP$39,320
CN¥722.08
List Price:  CN¥1,010.95
You save:  CN¥288.86
RD$5,924.54
List Price:  RD$8,294.60
You save:  RD$2,370.05
DA13,479.97
List Price:  DA18,872.49
You save:  DA5,392.52
FJ$225.66
List Price:  FJ$315.94
You save:  FJ$90.27
Q780.13
List Price:  Q1,092.22
You save:  Q312.08
GY$20,949.30
List Price:  GY$29,329.86
You save:  GY$8,380.56
ISK kr13,941.60
List Price:  ISK kr19,518.80
You save:  ISK kr5,577.20
DH1,013.74
List Price:  DH1,419.27
You save:  DH405.53
L1,764.37
List Price:  L2,470.19
You save:  L705.82
ден5,702.11
List Price:  ден7,983.18
You save:  ден2,281.07
MOP$806.29
List Price:  MOP$1,128.85
You save:  MOP$322.55
N$1,909.24
List Price:  N$2,673.01
You save:  N$763.77
C$3,682.20
List Price:  C$5,155.22
You save:  C$1,473.02
रु13,347.49
List Price:  रु18,687.02
You save:  रु5,339.53
S/372.08
List Price:  S/520.93
You save:  S/148.85
K382.87
List Price:  K536.04
You save:  K153.16
SAR375.15
List Price:  SAR525.22
You save:  SAR150.07
ZK2,481.24
List Price:  ZK3,473.83
You save:  ZK992.59
L460.94
List Price:  L645.33
You save:  L184.39
Kč2,346.52
List Price:  Kč3,285.22
You save:  Kč938.70
Ft36,532.34
List Price:  Ft51,146.74
You save:  Ft14,614.40
SEK kr1,069.05
List Price:  SEK kr1,496.71
You save:  SEK kr427.66
ARS$85,802.56
List Price:  ARS$120,127.02
You save:  ARS$34,324.45
Bs691.35
List Price:  Bs967.92
You save:  Bs276.56
COP$386,901.39
List Price:  COP$541,677.43
You save:  COP$154,776.03
₡50,320.66
List Price:  ₡70,450.94
You save:  ₡20,130.27
L2,469.73
List Price:  L3,457.73
You save:  L987.99
₲739,686.52
List Price:  ₲1,035,590.72
You save:  ₲295,904.20
$U3,756.16
List Price:  $U5,258.77
You save:  $U1,502.61
zł399
List Price:  zł558.62
You save:  zł159.61
Already have an account? Log In

Transcript

Another common mistake professionals make while writing below cap and match combo formula is that sometimes in a hurry, they forget to write the zero of either v lookup or match. And because of which match formula assumes it's an approximate match. And that's the reason why even if I press enter, I am not getting any answer. That is correct. In fact, I'm getting the same company in orange. So I need to make sure that if it is an exact match that I'm trying to look for, ensure that you put zero in fact, the best way to add the formula.

Now combining all the logics that we have learned in the last few videos is equal v lookup. Look for that value amongst the two which is placed vertically in the first column of your data set. That is orange, comma. Next is table array and ensuring the prerequisites of the lookup is satisfied, which is the first column must contain the common link. I choose the entire block. I immediately press f4.

So typically good practice session. At any array that you are supposed to choose you fix it, comma. Next, I will reserve a seat for match. reserving a seat means I'm just going to put a blank placeholder for match formula. This ensures that you are able to minimize the syntax errors while writing combo formula. Comma, I need to remember that V lookup needs a zero for exact match.

So this is the framework for the lookup and match. To have the final portion completed. Let me get inside the parenthesis of match formula. The focus areas purely going to be on the header and remember, Junior follow senior from one of our previous videos, that is the logic we are going to follow. So match look for something that one term which you will be able to find in the header to match lookup for cost. Comma, then making sure Junior follow senior is followed, choosing the header, immediately fixing it and also to ensure that match also needs a zero for the Exact Match calculation.

So practically there are two zeros when you are having a normal v lookup and match as I press enter, and just to test whether it's working and writing sales, if I write read, yeah, there we go. So two dimensional, two elements, helping you find one answer from the dig.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.