how to use vlookup in excel 2010 to compare two sheets

HOW TO USE VLOOKUP IN EXCEL 2010 TO COMPARE TWO SHEETS HOW TO

Choose Exact match for the match_type parameter.Insert 5 as the col_index_number argument as we are looking to retrieve data from the 5th column from our table.Select the range of cells B5:F17 which defines the table where the data is stored (the table array argument).

To write the VLOOKUP formula in cell F2 follow these steps:

HOW TO USE VLOOKUP IN EXCEL 2010 TO COMPARE TWO SHEETS HOW TO

Video can't be loaded because JavaScript is disabled: How to use VLOOKUP in Excel () How to write a VLOOKUP formula Here's a quick video from Microsoft that demonstrated how to write a VLOOKUP formula. If you want to be on the safe side I strongly recommend that you always input a value for your match_type argument. Since the match type parameter is optional please remember that Excel uses Approximate match by default. I can safely say that probably around 95% of your VLOOKUP formulas will use Exact match. Because we are looking for a specific employee ID we need to use Exact match. The final step is to choose Exact match or Approximate match. This is important! Since we need the Annual Salary (which is stored on column F) we will use 5 as col_index_num argument. Keep in mind that this refers to the number of columns from the table array range and not the Excel column number.

The third step is to check the column number from where we want to retrieve the info. In the image above example, this is table B5:F17 and this range is the table array argument. This means approximate match is the default mode, which can be dangerous.The second step is to select the data where this info is available. Important: range_lookup is optional defaults to TRUE. When range_lookup is FALSE, VLOOKUP performs an exact match, as in the example above. A good example of this is using VLOOKUP to calculate grades. The word 'range' in this case refers to 'range of values' – when range_lookup is TRUE, VLOOKUP will match a range of values rather than an exact value. VLOOKUP has two modes of matching, exact and approximate, controlled by the fourth argument, range_lookup. To lookup values to the left, see INDEX and MATCH, or XLOOKUP. VLOOKUP can only look to the right. In other words, you can only retrieve data to the right of the column that holds lookup values: Above, the references are relative to make them easier to read. Note: normally, we would use an absolute reference for H3 ($H$3) and B4:E13 ($B$4:$E$13) to prevent these from changing when the formula is copied. = VLOOKUP (H3 ,B4:E13, 2, FALSE ) // first name = VLOOKUP (H3 ,B4:E13, 3, FALSE ) // last name = VLOOKUP (H3 ,B4:E13, 4, FALSE ) // email address For example, to get the name for order 1004, the formula is: ID, Amount, Name, and State for any order. With the Order number in column B as the lookup_value, VLOOKUP can get the Cust. The purpose of VLOOKUP is to look up information in a table like this: See below for more information on matching. Important: range_lookup is optional and defaults to TRUE, so VLOOKUP will perform an approximate match by default. If range_lookup is FALSE, VLOOKUP will perform an exact match. If range_lookup is TRUE, VLOOKUP will perform an approximate match. Finally, range_lookup controls match behavior. The column_index_num argument is the column number of the value to retrieve, where the first column of table_array is column 1. The first column of table_array must contain the lookup values to search. Lookup_value is the value to look for, and table_array is the range of vertical data to look inside. VLOOKUP takes four arguments: lookup_value, table_array, column_index_num, and range_lookup. The key to using VLOOKUP successfully is mastering the basics.

In addition, VLOOKUP's default matching behavior makes it easy to get incorrect results. This makes it hard to use VLOOKUP with multiple criteria. Unlike INDEX and MATCH (or XLOOKUP), VLOOKUP needs a complete table with lookup values in the first column. On the bad side, VLOOKUP is limited and has dangerous defaults. Using VLOOKUP successfully is a rite of passage: from beginner to skilled Excel user. For new users in particular, it is immensely satisfying to watch VLOOKUP scan a table, find a match, and return a correct result. On the good side, VLOOKUP is easy to use and does something very useful.

VLOOKUP is probably the most famous function in Excel, for reasons both good and bad. Vertical data | Column Numbers | Only looks right | Matching Modes | Exact Match | Approximate Match | First Match | Wildcard Match | Two-way Lookup | Multiple Criteria | #N/A Errors | Videos Introduction VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first column of the table passed into VLOOKUP. VLOOKUP is an Excel function to get data from a table organized vertically.

How to use vlookup in excel 2010 to compare two sheets