ExcelHub - Learn Excel Easily

Excel Tip #021 Quickly Identify Column Differences If you need to compare two or more columns of data, there are various methods you can use in Excel to check for differences. Sorting, Conditional Formatting and using formulas such as MATCH or VLOOKUP are just a few of them. For years, whenever I needed to compare two columns of data, I used a simple formula such as =B2=C2 that would give me TRUE if the cells are the same and FALSE if they aren't. But there's and even easier and faster way identify differences in columns of data without using formulas. Select the columns to compare Press press CTRL + \ (or press F5, Special.., Row Differences , OK). Cells in the 'non-control' column(s), whose values don't match the values in the control column, are selected Add a background Fill color to make the selected cells more visible For 2-column and multiple row comparisons, Right-click one of the highlighted cells and choose Filter, Filter by Selected Cel...

Excel Tip #013 VLOOKUP - Exact Match & Approximate Match In the previous tip #012 (VLOOKUP & INDEX + MATCH), we learnt about VLOOKUP, it's syntax and how to use it. We also learnt about its drawback and how INDEX & MATCH can be helpful. Like said in the previous tip, there are 2 match option available in VLOOKUP - Exact & Approximate Match . Let us first refresh the syntax of VLOOKUP. Syntax: = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) In simple terms, =  VLOOKUP (value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match) Let's have a closer look at the last argument you supply to the Excel VLOOKUP function - range_lookup. You are likely to get different results in the same formula depending on whether you enter TRUE or FALSE (Approximate or Exact). If range_lookup is set to FALSE , the formula searches for exact match, i.e. for the lookup value exa...

Excel Tip #012 VLOOKUP & INDEX + MATCH Excel has many formulas which functions differently. To analyze data, one of the most important formula is VLOOKUP and INDEX + MATCH . Let us now get into the details of it. VLOOKUP : Let us consider the below data as an example. Sales Data Let us say, we want to know how much sales did Jerald make?  VLOOKUP  can be used to answer this question. What does VLOOKUP actually do?      VLOOKUP searches a list for a value in left most column and returns corresponding value from adjacent columns. Syntax: = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) In simple terms, = VLOOKUP (value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match) The final parameter is optional but very important. In the next tutorial, I will provide a few examples explaining how to correctly make formulas for exact (or) ...