Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

How to use VLOOKUP to create a unique list from table in Excel?

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?
doc vlookup unique list 1

Use VLOOUP to create a unique list from given table

quickly convert multiple XLSX. files to XLS or PDF. files in once time

In many cases, you may want to convert between XLSX and XLS, or convert a workbook to PDF file, but in Excel, you only can convert one workbook once time, have you ever imaged to convert multiple workbooks at once time? With Kutools for Excel's File Format Converter, you can quickly convert multiple workbook to pdf/97-2003 workbook/2007 or higher workbook at once and save them in a folder, at the same time, a converted report is generated!  Click for 60 days free trial!
doc file converter
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

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:
doc vlookup unique list 2

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.


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.