Get a month of TabletWise Pro for free! Click here to redeem 
TabletWise.com
 

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

Software Microsoft Excel Advanced Excel Crash Course Section 11: Lookup and Conditional Calculations
2 minutes
Share the link to this page
Copied
  Completed
You need to purchase the class to view this lesson.
One-time Purchase
$99.99
List Price:  $139.99
You save:  $40
د.إ367.26
List Price:  د.إ514.18
You save:  د.إ146.92
A$143.97
List Price:  A$201.56
You save:  A$57.59
৳8,483.46
List Price:  ৳11,877.18
You save:  ৳3,393.72
CA$135.49
List Price:  CA$189.69
You save:  CA$54.20
CHF 94.55
List Price:  CHF 132.38
You save:  CHF 37.82
kr662.45
List Price:  kr927.46
You save:  kr265.01
€88.91
List Price:  €124.49
You save:  €35.57
£80.09
List Price:  £112.13
You save:  £32.04
HK$774.92
List Price:  HK$1,084.92
You save:  HK$310
₹7,467.19
List Price:  ₹10,454.37
You save:  ₹2,987.17
RM428.70
List Price:  RM600.20
You save:  RM171.50
₦38,796.12
List Price:  ₦54,316.12
You save:  ₦15,520
kr947.45
List Price:  kr1,326.46
You save:  kr379.01
NZ$153.02
List Price:  NZ$214.23
You save:  NZ$61.21
₱4,955.30
List Price:  ₱6,937.62
You save:  ₱1,982.32
₨16,755.82
List Price:  ₨23,458.82
You save:  ₨6,703
S$139.49
List Price:  S$195.29
You save:  S$55.80
฿3,111.82
List Price:  ฿4,356.68
You save:  ฿1,244.85
₺686.22
List Price:  ₺960.74
You save:  ₺274.51
R1,701.27
List Price:  R2,381.85
You save:  R680.57
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