Formatting Issues

How to Show Each Person Next to Their Certification in Excel

Maira Mehtab Sep 24, 2026 871 views

Question details

The user needs to organize an Excel layout so that each person's name is accurately displayed next to their corresponding certification.

Product: Excel
Device & OS: not provided
Scenario: Formatting or structuring a personnel and certification tracker to clearly map individuals to their achievements.
Observed behavior: The initial data structure was unclear, requiring appropriate layout techniques, formulas, or pivot tables to properly pair the names with the certification data.

Before you start

Ensure your raw data is organized clearly in columns without blank rows, and that names are spelled consistently across different sheets to avoid mapping errors.

Solution 1Recommended

Use VLOOKUP to Match Certifications to Names

Using a VLOOKUP formula allows you to automatically pull certification data from a master list and display it exactly next to the individual's name.

If you have a master list of certifications on one sheet and a list of employee names on another, a lookup formula is the most efficient way to bring the data together.

When asking for help in forums regarding lookup issues, it is always recommended to provide a sample file, your Excel version, and current formulas.

Prepare your source data

Verify that you have a source table with employee names in the first column and their certifications in the second column.

Enter the VLOOKUP formula

In your target worksheet, click the cell next to the person's name and enter =VLOOKUP(A2, Sheet2!A:B, 2, FALSE), replacing A2 with the name cell and Sheet2!A:B with your source data range.

Copy the formula down

Select the cell with the formula, click the small square at the bottom-right corner of the cell, and drag it down to apply the lookup to the rest of the names.

Troubleshooting Data Layouts: If the desired layout is complex, providing a mockup or sample test file showing your expected result helps determine whether a formula or a Pivot Table is the better approach.

Solution 2

Group Names by Certification Using a Pivot Table

If your goal is to see a categorized list of certifications with the respective names listed under or next to them, a Pivot Table is the best feature to use.

WPS Spreadsheet Formatting

Easily Match and Format Spreadsheet Data with WPS Office

WPS Spreadsheet provides powerful data matching tools, advanced lookup functions, and intuitive Pivot Tables to seamlessly organize names and certifications. It is an excellent, lightweight alternative for managing complex datasets.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open the file containing your personnel and certification data.
2. Use the Insert Function tool: Click on the 'Formula' tab and select 'Insert Function' to easily search for and apply lookup formulas like VLOOKUP or XLOOKUP.
3. Group data with Pivot Tables: Navigate to the 'Insert' tab and click 'PivotTable' to quickly categorize your employee list by their certifications for a cleaner layout.

Fully compatible with Microsoft Excel (.xlsx) file formats and lookup functionsIntuitive formula builder to easily match names with their respective certificationsLightweight application with robust Pivot Table data grouping features

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning an #N/A error when mapping names?

An #N/A error typically means the exact name cannot be found in your source table. Check for hidden spaces, spelling discrepancies, and ensure your lookup range is properly locked using absolute references (e.g., $A$2:$B$100).

How do I ask for help with complex Excel layouts online?

To get the best help, provide a sample or test file, clearly describe your current table structure, state the exact version of the spreadsheet software you are using, and include a mockup showing your expected final layout.

Can I show multiple certifications for one person in a single cell?

Yes. If an individual has multiple certifications, you can use the TEXTJOIN function combined with an IF array or the FILTER function to merge all associated certification names into one cell next to their name.

Document & File Issues