MS Excel - VLookup Approximate Match

in this video we'll be looking for we look up with approximate match the meaning of 0 & 1 the 0 & 1 the fourth parameter in the vlookup let's see that what it is for explanation I've written some dummy data here that is if you see I have sales amount category and Commission suppose there is an employee working in a company and if it does a sales of let's say $2,000 he'll get the Commission of let's say $40 and the category it belongs to okay category similarly if I do a sales of let's say $5,000 I'll get a commission of $500 and very good as the category but in a company it's not exactly like people will do a sales of $5,000 he might do as sales of 4500 dollars or let's say $2,500 so what category he will fall under so let's see how we can calculate so at the bottom I have written here a function I have written here sales then if sales done by a person is this much what is the Commission pay so let's see how we can do it with a vlookup approximately match so I'll write here a function as equal to V lookup lookup value comma my table we'll start from this place because this lookup value should belong to the first column of the table day array comma and I have something here column index number with respect to table I have first second and third three comma now instead of 0 I'll put here answer s1 because I want to find out the approximate what is the answer and I'll press Enter so it says for $1000 there's no Commission that is 0 right 0 so let's see what is the difference I'll just type here as 2000 so for 2000 the answer it gave us 40 which is absolutely correct if I if I do a sales of let's say $5,000 I'll get the Commission as 500 what if a person does sales less than 500 if I do a say it's less than 500 let's say $4,500 and I press enter so let's see if I do a sales of 4500 I am getting a commission of $200 so if you do even a single sale less than this 5000 it goes up and you'll get the amount as $200 similarly if I do a sales less than $3,000 2,500 now if I press ENTER what I'll get is I'll get a commission of $40 I'll press Enter so you see there's a $40 if I do a sales less than 2000 that is let's say 1000 and press Enter so less than 1000 if you do I'll get a commission of $0 so approximate matches used to give the approximate answers nearby somewhere so let's see how much you'll get a commission of $40 so you'll get a commission of $40 if you do a sales of $2000 - mm mm dollars that is $2,000 to to triple $9.00 so if you see on the left hand side I have written here if you do a sales of 2,000 and less less than 3000 that is to triple nine so you'll get a commission of how much $40.00 so on the left hand side there's an explanation given and these are the amount you'll get it now it has some rules given here for example if I change it to let's say $10 yeah if I do a sales of less than the first one if I do a sales of less than $10 if I do a sales of $2 what is the answer I'll get this I'll get the answer as not available because it's nothing about that so if you see the first one less than the first value in the lookup column this base value as not available second is in this lookup value column I can only have the numbers I cannot have the text so how it should be this numbers in the first lookup column should be sorted in the ascending to descending order you cannot take right enum or text here but you have to write numbers and that doing ascending to descending order next one is if I if I type here two thousand dollars I'm getting the answer is 40 which is absolutely correct now if I remove this parameter instead of one if I remove remove this parameter the fourth argument if you see it's a range lookup that's in square brackets if I remove that what is the answer I'll get it I'll get the answer still the same so by default if you forget the argument in this it will consider as it an approximate match so I have written here leaving fourth argument blank makes it as approximate match so even if you don't write it's still the approximate match so I hope these are the rules and how we can go with we look up with approximate so that's all for this video thank you

Channel: Tutorials Point (India) Ltd.

Views: 237,477

Keywords: MS Excel, Excel, Microsoft, MS, MS Excel - VLookup Approximate Match, VLookup Approximate Match

Id: ZwUSZvbdo9E

Length: 4min 15sec (255 seconds)

Published: Mon Jan 15 2018