Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to create dynamic named range with OFFSET in Excel
  • How to use Excel MMULT Function
  • How to get last row in text data in Excel
  • How to use Excel MATCH Function
  • How to use Excel COLUMN Function

Data Analysis Examples

  • Use Data Form to input, edit and delete records in Excel
  • How To Create Pareto Chart in Excel
  • Understanding Pivot Tables in Excel
  • How to create dynamic reference table name in Excel
  • How to count table columns in Excel

Data Validation Examples

  • Excel Data validation must contain specific text
  • Excel Data validation allow uppercase only
  • Excel Data validation must not contain
  • Excel Data validation don’t exceed total
  • Excel Data validation specific characters only

Group arbitrary text values in Excel

by

This tutorial shows how to Group arbitrary text values in Excel using the example below;

Formula

=VLOOKUP(value,table,column,0)

Explanation

IF you want to group or classify data based on arbitrary text values, you can use VLOOKUP instead of a long series of nested IF statements.

Worked Example:   Exact match lookup with INDEX and MATCH in Excel

The trick is to build a custom table that will map values to all the groups you need.

In the example shown, the formula in F5 is:

=VLOOKUP(E5,key,2,0)

How this formula works

This formula uses the value in cell E5 for a lookup value, the named range “key” (H5:I9) for the lookup table, 2 to indicate “2nd column”, and 0  as the last argument indicate exact match. You can also use FALSE instead of zero if you like.

Worked Example:   How to translate letters to numbers in Excel

VLOOKUP simply looks up the value and returns the group name from the 2nd column in the table. Both columns in the table can contain any values that you need, and the table doesn’t need to be sorted since VLOOKUP is using exact match.

Worked Example:   Partial match with VLOOKUP in Excel

As always, if VLOOKUP can’t find a match, it will return #N/A.

Post navigation

Previous Post:

Categorize text with keywords in Excel

Next Post:

Group numbers with VLOOKUP in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to count total words in a range in Excel
  • Two ways to Compare Text in Excel
  • DBCS function: Description, Usage, Syntax, Examples and Explanation
  • Count Number of Words in Excel
  • Convert text to numbers using VALUE function in Excel
  • Get days, months, and years between dates in Excel
  • Get last working day in month in Excel
  • Convert decimal hours to Excel time
  • Get age from birthday in Excel
  • Generate series of dates by weekends in Excel
  • Calculate payment periods for loan in Excel
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
  • DOLLARDE function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles