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

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

Software Microsoft Excel Advanced Excel Crash Course Section 11: Lookup and Conditional Calculations
3 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$201.68
You save:  A$57.62
List Price:  ৳11,877.18
You save:  ৳3,393.72
List Price:  CA$189.83
You save:  CA$54.24
CHF 94.49
List Price:  CHF 132.29
You save:  CHF 37.80
List Price:  kr927.95
You save:  kr265.14
List Price:  €124.52
You save:  €35.58
List Price:  £112.26
You save:  £32.07
List Price:  HK$1,084.92
You save:  HK$310
List Price:  ₹10,448.81
You save:  ₹2,985.59
List Price:  RM600.20
You save:  RM171.50
List Price:  ₦54,316.12
You save:  ₦15,520
List Price:  kr1,328.81
You save:  kr379.68
List Price:  NZ$214.27
You save:  NZ$61.22
List Price:  ₱6,927.65
You save:  ₱1,979.47
List Price:  ₨23,458.82
You save:  ₨6,703
List Price:  S$195.16
You save:  S$55.76
List Price:  ฿4,356.48
You save:  ฿1,244.80
List Price:  ₺961.07
You save:  ₺274.61
List Price:  R2,385.35
You save:  R681.57
Already have an account? Log In


Let's talk more about VLOOKUP and match especially the intricacies that you must be aware of. Although we wrote this formula in our last video session, he saw how v lookup was capturing the entire table array, as indicated by the red border on my screen. And how much is trying to find the word cost in the small border color, pink border color region and giving the town or sales to for cost three. Now there's some things you need to be aware of while writing will look a match in a bigger data set. And what I've done is I have tried to use mnemonic to help you remember that technique. Let's consider match to be Junior formula, and we look up to the senior formula.

Why? Because table array is what we look up requires. ability is what we will requires, and it can take the whole set of data including multiple rows and columns, but in match, the term uses lookup array which means is only one dimension. And that is the reason I'm referring notch as a junior formula and V lookup as senior formula. Now as per the logic when you combine these two Formula One thing to be aware of is Junior follows senior. Yes, the formula Junior, which is match should not exceed the array selection or array area of the lookup or the SR formula, nor should it start later and definitely cannot take the entire table array.

In simple words, we are going to refer to this logic of junior follow sr, I tell you immediately what would happen if you choose extra data or less data like now, if if I go to our V lookup match formula, which we had written in one of our previous videos, let's assume that I'm deleting the selection of lookup array for match and these selecting this tank taking extra cells. I'm going to press f4 and before I press enter and see the answer, I want you to ponder Something that is, if you're asking match formula to look for the world cost in this blinking border area, what would be the count? One? No 234 Yes, it is going to be four, if match fetches you the digit four, and this four becomes an input for V lookup, there will be a lookup table array, look into first column, second column, third column and fourth column, but there is no fourth column selection.

And because of which, if I press enter, notice reference era. This is one of the most common problems, the selection of area for VLOOKUP. And match is not synchronized. In fact, let me also show you one quick example where I'll be taking the cell selection a little less than what is required. For this case, match and not choosing Junior follow senior principal, I am starting from the word sales and only ending it cost at this moment. The cost the count of cost is to one in two and if you give me digit two, two v lookup v lookup will look into which column, second column.

So if I now press f4 and press enter, your answer will be taken from the second column, which is 4500. in both the cases, we saw that if match is not in sync with V lookup, you will not get the correct answer. In simple words, whenever you are using VLOOKUP and match, remember, Junior follow senior, and most importantly, where exactly do we need to use the lookup and match especially when you have two dimensional data set? One is placed vertically, one is placed horizontally and intersection of two elements is what is supposed to fetch you the answer. The answer could be probably here, and it could be marked by two variables, which will help fetch you the answer. That was the lookup and match

Sign Up