Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to calculate correlation coefficient Correlation in Excel
  • How To Create Frequency Distribution in Excel
  • How to Create Column Chart in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • Number and Text Filters Examples in Excel

References

  • How to get address of named range in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to use Excel MATCH Function
  • Find closest match in Excel
  • Count rows with at least n matching values

Data Validations

  • Data validation must not exist in list
  • Excel Data validation must begin with
  • Excel Data validation allow uppercase only
  • Excel Data validation with conditional list
  • Excel Data validation don’t exceed total

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:   Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

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:   Excel Advanced Lookup using Index and Match Functions

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:   Get first text value in a list in Excel

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

Post navigation

Previous Post:

VLOOKUP without #N/A error in Excel

Next Post:

How to Create Gantt Chart 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

Logical Functions

  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • IF with wildcards in Excel
  • IF with boolean logic in Excel
  • Extract multiple matches into separate rows in Excel
  • Not Equal To ‘<>‘ operator in Excel

Date Time

  • Get days between dates ignoring years in Excel
  • How to calculate workdays per month in Excel
  • Convert text to date in Excel
  • Convert date to month and year in Excel
  • Count day of week between dates in Excel

Grouping

  • Map text to numbers in Excel
  • Group numbers at uneven intervals in Excel
  • Calculate conditional mode with criteria in Excel
  • Running count group by n size in Excel
  • If cell contains one of many things in Excel

General

  • Sum by group in Excel
  • How to password protect excel sheet?
  • How to create dynamic named range with INDEX in Excel
  • How to make excel worksheets print on one page?
  • Count cells that do not contain many strings in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning