Fwd: Most people think there’s only one way to use Excel’s VLOOKUP

[Kuldeep Singh]

---------- Forwarded message ----------
From: My Online Training Hub <web...@myonlinetraininghub.com>
Date: Tue, Oct 22, 2013 at 9:16 AM
Subject: Most people think there’s only one way to use Excel’s VLOOKUP
To: Kuldeep Singh <naukri...@gmail.com>

Home | Blog | Login

Hi Kuldeep, VLOOKUP is my favourite Excel Formula! It helps me get analysis and calculations done in minutes that would take hours manually. And I find I have the occasion to use it all the time.   Interestingly there's two ways you can use it but I find that most people know one way or the other, and only a few know both.   One way to do a VLOOKUP is what I call the Sorted List VLOOKUP, but more on that soon.   In this tutorial we're going take a look at the VLOOKUP Exact Match version of the formula, but first let's set the scene.   In the list below we want to calculate a commission in column F for each builder. But each builder has a specific commission rate they are entitled to.   Thankfully we have this information in a table to the right, and this is where we give VLOOKUP the opportunity to strut its stuff.     Microsoft Excel describes the VLOOKUP formula as:   VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)   And to translate it into English it would read:find this value, in that table, return the value in column x of the table, but only return a result if you can match the value exactly)   Let's make it even clearer by applying it to our example:   VLOOKUP(find the name Doug from cell B2, in the Commission Rates table H2:I9, return the value in column 2 of the table, but only return a value if you find the exact name Doug in the Commission Rates table, otherwise give me an error)     Firstly let me clarify some points:   1) 'Return the value in column 2 of the table' is referring to the column number in the table H2:I9, not the column number of the spreadsheet. The information we want returned is the percentage rate, and it is in the second column of the Commission Rates table.   2) 'Only return a result if you can match the value exactly' is telling Excel that we only want information returned if it matches our criteria exactly. i.e. Find Doug in our Commission Rates Table, and if you can't find Doug, give me an error. The error displayed will be #N/A.   On the other hand, if we told Excel it was ok to not find an exact match, it would return the next best result. i.e. If Doug wasn't in our Table Excel would return the next best result. In this example we wouldn't want it to do that, but this option is handy in other situations which we'll cover in another tutorial.   OK, now that's clarified, in Excel our VLOOKUP formula in column F for the above example would be:   =VLOOKUP(B2,$H$2:$I$9,2,FALSE) Note: Where 'FALSE' is telling Excel we want it to find an Exact Match only.   Our Excel table would then look like this with the VLOOKUP formula in column F:     You'll notice in the formula bar above there are '$' signs around the reference to the table H2:I9. This is called an absolute reference and it allows us to quickly copy the formula down column F without Excel dynamically updating the table range as we copy.   How can we make this VLOOKUP formula even better?   Assuming the end result of our example exercise is to actually calculate the commission $ amount, we could make this formula even better by doing this in one step in column F.   Let's say commission is calculated as Total $k x Commission %, our formula in cell F2 would read:   =VLOOKUP(B2,$H$2:$I$9,2,FALSE)*E2   And in seconds we can have hundreds of VLOOKUP's and calculations done!     VLOOKUP Rules, Common Mistakes and Troubleshooting!   1) VLOOKUP formulas read from left to right. You must have the information you are looking up (in our example Doug in the Commission Rates Table), in a column to the left of the information you want returned, in our example the 'percentage rate'. i.e. it has to go 'Doug', then '% rate'. Excel wouldn't be able to find it if it went '% Rate' then 'Doug'.   2) You can have as many columns as you like in your Table, just so long as you follow the 'left to right' rule above.   3) The 'Table' you are looking up can be in the same spreadsheet, or a different sheet in the same workbook, or in a different workbook altogether.   4) The table doesn't have to be sorted in any particular order, but you must not have duplicates, unless the information on each duplicate is exactly the same. For example, if Doug appeared twice in our Commission Rates table with different percentage rates for each instance, Microsoft Excel would return the rate on the first instance of Doug.   5) The formula isn't case sensitive, so 'Doug' could be 'doug' or 'Doug' in either column B or the table.   6) What doesn it mean when my VLOOKUP returns a #N/A? It means Excel can't find the value you're trying to look up in your table.   If you get this, but you can 'plain as day' see it's there in the table. Then it's likely you've got one value formatted as Text and another formatted as General.   To check this go to each cell you're referencing and look in the formula bar to see if one is prefixed by an apostrophe '. You can only see the apostrophe from the formula bar. See example below.   Basically Excel reads text prefixed with an apostrophe as different to text without, even though on the face of the spreadsheet they might look the same. You need to make sure both the value you're looking up, and the value in the table either both have the apostrophe, or both don't.   The quickest way to get rid of the apostrophes is to do 'Text to Columns', but that lesson is for another day.   7) This formula works the same in both Microsoft Excel 2003 and Microsoft Excel 2007.   The VLOOKUP is a fairly basic formula, but its applications are vast, especially when you combine it with other formulas like IF statements, SUMIF and so on.   Download the Excel workbook used in this example so you can copy and practice the VLOOKUP formula to make sure you really get it and can take advantage of its power.   Did you like this tutorial or could it have been better? Click here to let me know your thoughts on the blog.   If you like what you learn at My Online Training Hub please share it with your friends on Facebook and follow us on Twitter. Have a great day.   Mynda. Co-Founder My Online Training Hub

PO Box 7206, Sippy Downs, QLD 4556, AUSTRALIA Unsubscribe | Change Subscriber Options

--

Regards,

Kuldeep Singh

Assistant Manager Quality

Game Zone India Limited

Phone.: +91-8586978644
|| naukri...@gmail.com ||
Please Consider the environment. Please don't print this e-mail unless you really need to.