A friend of mine asked me to help him find the Excel worksheet and the repetition of the data listed in the cell. I have give him a formula for reference, but not too much to meet the requirements, because the formula to identify duplicate data while, but if the array to copy the formula down when the data exceeds the number of duplication, will be in the corresponding cell error. Shortly afterwards, my friend found a better formula. This formula is very good, very good to solve such problems, are interested in friends for reference.

Listed in column A and B exist in a series of data (the table is only an example, there may be a lot of data), request to identify a person (that is, out in the name of A) corresponding to all the training records (that is, the data in the column B ). In other words, in cell E1, type the person’s name, the following will automatically show all the people training records.

We know, Excel’s LOOKUP function series can easily find, but returned to find the results of a series of such function could do nothing about it, so other functions can only be achieved jointly.

Here, a method used in the INDEX function, SMALL function, IF function and the ROW function, in the second approach, also used in the new Excel 2007 functions IFERROR.

Method One:

1, select cell E3;

2, enter the formula: = INDEX (B: B, SMALL (IF ($ A $ 2: $ A $ 25 = $ E $ 1, ROW ($ A $ 2: $ A $ 25), 65536), ROW (1:1))) & “”, and then press Ctrl + Shift + Enter key, or enter the array formula.

3, select the drop-down cells after E3 to all cells.

Method Two:

1, select cell F3;

2, enter the formula: = IFERROR (INDEX ($ A $ 2: $ B $ 9, SMALL (IF ($ A $ 2: $ A $ 9 = $ E $ 1, ROW ($ A $ 2: $ A $ 9)-ROW ($ A $ 2 ) +1, ROW ($ A $ 9) +1), ROW (1:1)), 2 ),”"), and then press Ctrl + Shift + Enter key, or enter the array formula.

3, select cell F3 after the drop-down to all cells.