How Excel tables use the VLOOKUP function to discover duplicate data

in a duplicate record of an Excel table, let you quickly list the 2nd or nth occurrences of each item, what would you do? The VLOOKUP function has this ability.

For example, a product or logistics form often records multiple data of the same cargo (the list of today's sales in the following figure is listed, the fridge has 3 big orders, other goods also have a large list, if you want to each of the goods of the second (or third) orders are automatically found, how to do?

Although this problem can only be solved with a formula, but the construction of such a formula needs to use the VLOOKUP function, if function array expression, countif function, indirect function, such as complex combination, if not Excel master, look also will be dizzy. People often say "long story short", and so is the question. We can combine the complex ideas of constructing formulas into short steps that are easy to understand by ordinary people, and can also find the "second", "third party" or any data in the list of items with several simple steps.

For easy identification and reference, we add two secondary columns to the table, where B is the item number, and the other column C is the numbered item (unique) that the number is generated with the item merge.

Enter the formula "=countif ($A $2:a2,a2)" in cell B2, and then populate the column with the lower right corner fill handle. The effect is to automatically populate the number of pen numbers to which each pen belongs.

Next, enter the formula "=A2&B2" in cell C2, and populate the column with the lower right corner fill handle.

Finally, we use the VLOOKUP function in the H3 cell to make a statistic for the name of the G-column user (the user wants to enter the first few strokes to the first few strokes). H3 cell Input function:

=vlookup (G3, $C $: $D $10,2,0)

The function is to automatically filter the values in column C and D in columns that meet the requirements of column G and fill in column H.

Then pull down the fill handle in the lower-right corner of the H3 cell to automatically extract the second data from the remaining items.

So what do you do if you want to find the 3rd, 4th, or any other single piece of data for each item? It's very easy! You can simply change the G column to 3 or other data manually, and the H column automatically shows you the data you need.

VLOOKUP function Magic Invincible! The basic syntax of the VLOOKUP function we have said many times before, "spiritual people can not be carefully", smart you must have already used. If a beginner needs to know the basics of this function, check out the function descriptions in Excel Help. Small weave is not a pen and ink.