Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,200 --> 00:00:12,250
In the previous lesson, we saw how we can use the look up with the exact match false argument on the
2
00:00:12,250 --> 00:00:14,500
end to return results.
3
00:00:14,830 --> 00:00:20,170
So in this lesson, we're going to look at the true argument, the approximate matching option.
4
00:00:21,070 --> 00:00:23,290
So what is the difference between these two?
5
00:00:23,500 --> 00:00:26,890
Well, you would use approximate matching for your V lookup.
6
00:00:27,040 --> 00:00:31,660
If you'll look up, value doesn't exist exactly in the table.
7
00:00:32,560 --> 00:00:33,880
So what do I mean by that?
8
00:00:34,150 --> 00:00:40,360
Well, if we take a look at the data that we have on this worksheet in this first table, I have effectively
9
00:00:40,360 --> 00:00:47,320
some salary bands and this is showing me the low end and the high end of this band and then the tax
10
00:00:47,320 --> 00:00:49,600
rate that applies to each of those bands.
11
00:00:50,380 --> 00:00:56,950
And what I want to do in these second table is I want to look up the salary and I want it to return
12
00:00:56,950 --> 00:00:59,410
the marginal tax rate for that salary.
13
00:00:59,620 --> 00:01:07,990
Now, if we take a look at the first one here, 55000 does 55000 exist exactly in this table over here?
14
00:01:08,200 --> 00:01:15,640
Well, if I take a look at my values, I can see, no, I don't have exactly the value 55000 listed
15
00:01:15,640 --> 00:01:15,910
here.
16
00:01:16,780 --> 00:01:20,620
This annual salary falls in this bracket just here.
17
00:01:20,920 --> 00:01:27,130
So if I was to do an exact match, it's not going to find it because the exact match argument, that
18
00:01:27,130 --> 00:01:32,230
false argument exactly matches the look up value in the table.
19
00:01:32,590 --> 00:01:37,930
So for this, I'm going to need to use the approximate matching option, which is the true argument
20
00:01:37,930 --> 00:01:38,530
on the end.
21
00:01:38,830 --> 00:01:43,810
Now I'm going to do exactly what I did in the last lesson, and I'm going to create a named range for
22
00:01:43,820 --> 00:01:47,110
my table simply to make this whole process a lot easier.
23
00:01:47,260 --> 00:01:50,110
So let's select the table in the name box.
24
00:01:50,110 --> 00:01:54,970
We're going to say tax underscore, right and enter.
25
00:01:56,670 --> 00:02:05,250
So now I can construct my villa up, so I'm going to type N equals v look up, my look up value is going
26
00:02:05,250 --> 00:02:07,050
to be the annual salary.
27
00:02:07,830 --> 00:02:15,210
The table array, well, I can the f three key and there is tax rates clicking okay.
28
00:02:15,600 --> 00:02:19,080
The column index numbers are what do I want to return here?
29
00:02:19,710 --> 00:02:26,790
Well, I'm looking for the tax rate, which is column number three, and this time we want to do an
30
00:02:26,790 --> 00:02:27,880
approximate match.
31
00:02:27,900 --> 00:02:30,270
So we want the true argument on the end.
32
00:02:30,300 --> 00:02:32,700
Alternatively, we could just type a one.
33
00:02:33,690 --> 00:02:35,670
Let's close the bracket and hit enter.
34
00:02:36,270 --> 00:02:43,500
And if I just double check this table, I can see that yes, 55000 falls in this range just here, and
35
00:02:43,500 --> 00:02:45,360
the tax rate is 25 percent.
36
00:02:45,600 --> 00:02:51,060
Let's do that one more time just to practice the look up, look up value.
37
00:02:52,100 --> 00:02:56,450
Is Cell E6 table array, we can press three.
38
00:02:56,480 --> 00:03:02,390
Alternatively, you can just start to type in your name range and it will appear in the list underneath
39
00:03:02,690 --> 00:03:05,260
double click to select Comma.
40
00:03:05,960 --> 00:03:12,290
We want the third column and we're doing an approximate match one on the end.
41
00:03:12,290 --> 00:03:14,120
This time close the bracket.
42
00:03:14,570 --> 00:03:17,600
Enter, and let's double check to make sure this is correct.
43
00:03:17,810 --> 00:03:23,630
This salary falls in this bracket up here, and the marginal tax rate is 10 percent.
44
00:03:23,870 --> 00:03:28,580
And just to show you how you can do this using the functions dialog box, if that is your preferred
45
00:03:28,580 --> 00:03:31,850
method, let's click on the Effects button.
46
00:03:32,630 --> 00:03:34,850
I'm going to search for V.
47
00:03:34,850 --> 00:03:35,390
Look up.
48
00:03:35,480 --> 00:03:36,470
Let's click Go.
49
00:03:36,890 --> 00:03:37,660
There it is.
50
00:03:37,670 --> 00:03:39,020
Double click to select.
51
00:03:39,470 --> 00:03:42,200
And now we can complete our arguments in this way.
52
00:03:43,160 --> 00:03:48,630
So the look up value for the final one is sell a table array.
53
00:03:48,650 --> 00:03:52,370
I can press the f three key and select tax rates.
54
00:03:53,390 --> 00:03:59,570
Column index number is three and the range lookup is true or a one on the end there.
55
00:04:00,410 --> 00:04:02,000
Let's click on OK.
56
00:04:02,030 --> 00:04:03,650
To complete that final one.
57
00:04:03,920 --> 00:04:10,160
And if I double check this while 90000 is going to fall in this range here and that has a tax rate of
58
00:04:10,160 --> 00:04:11,190
28 percent.
59
00:04:12,020 --> 00:04:17,570
So that is really the only difference between VLA Cup exact and filler cap approximate.
60
00:04:18,050 --> 00:04:24,620
It all relates to the lookup value and whether that look up value exists exactly in the table or if
61
00:04:24,620 --> 00:04:25,160
it doesn't.
5864
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.