Here is a table containing class and the student names. However, some values in the list are duplicates. What I want is to have a list of unique names based on “Class A” as below screenshot shown. How can I handle this job by using VLOOKUP function in Excel?
Use VLOOUP to create a unique list from given table
Here is a somewhat long VLOOKUP formula that can deal with the job.
Select a cell below the criterion which you want to create the unique list based on, type this formula =IFERROR(INDEX(B$1:B$13, MATCH(0, COUNTIF(D$1:D1, IF(A$1:A$13=D$1,B$1:B$13,D$1)), 0)),””), press Shift + Ctrl + Enter keys to get the correct value, and then drag fill handle down until blank cell appears. See screenshot:
Note: In the formula, B$1:B$13 is the column you will lookup values from, A$1:A$13 is the column you will lookup value based on, D$1 is the cell with the specified criteria.