Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Group arbitrary text values

While working with Excel, one of the easiest ways to retrieve data from another table is by using the VLOOKUP function. This step by step tutorial will assist all levels of Excel users in grouping arbitrary text values with the help of the VLOOKUP function.  

Figure 1. Final result: Group arbitrary text values

Final formula:  =VLOOKUP(C3,$F$3:$G$7,2,FALSE)

Syntax of the VLOOKUP function

VLOOKUP is used when we want to look up a value and retrieve data from a given data set.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The parameters of the VLOOKUP function are:

  • lookup_value – the value that we want to search and find in the table_array
  • table_array – the range of cells in the source table containing the data we want to retrieve
  • col_index_num the column number in the table_array corresponding to the information we want to retrieve, relative to the lookup_value
  • [range_lookup] – optional; value is either TRUE or FALSE
    • if TRUE or omitted, VLOOKUP returns either an exact or approximate match
    • if FALSE, VLOOKUP will only find an exact match

Setting up Our Data

Our table contains a list of Item ID (column B) and Material (column C). In column D, we want to   group the materials, whether recyclable or non-recyclable, based on the values in table F2:G7. Cells F2:G7 contain the list of Material (column F) and the corresponding Group per material (column G).

Figure 2. Sample data to group arbitrary text values

Group the items using VLOOKUP

We want to group the items according to their material, based on the list in table F2:G7.  In order to populate column D with the values from table F2:G7 using VLOOKUP, we follow these steps:

Step 1.  Select cell D3

Step 2.  Enter the formula: =VLOOKUP(C3,$F$3:$G$7,2,FALSE)

Step 3:  Press ENTER

Step 4:  Copy the formula in cell D3 to cells D4:D9 by clicking the “+” icon at the bottom-right corner of cell D3 and dragging it down

The dollar signs “$” in the formula fix the cells so that we can easily copy and paste the formula to other cells.  

Figure 3. Entering the formula to group materials using VLOOKUP

Our lookup_value is cell C3 which is the material.  The table_array is the range F2:G7, where we want to lookup the group per material.  “Group” is in the second column of the table_array so col_index_num is 2.  Range_lookup is FALSE because we want to find the exact match.  

The final result in cell D3 is Recyclable, which is the group where the material “Plastic” belongs.  

Copying the formula to the succeeding cells finally groups all the items according to their material, whether “Recyclable” or Non-recyclable”.  

Figure 4. Output: Group items according to their material

Note:  When VLOOKUP doesn’t find a match to our lookup value, the function returns the value #N/A.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I'm looking for a formula or format for if there is data in c4 on a sheet... It adds extra information underneath
Solved by X. E. in 20 mins
how to make a format that if multiple cells have number contents its value is 1 in the last cell
Solved by C. C. in 28 mins
I filtered data in column A (it is labeled ID #) of a data set. On my next worksheet, column A is also the ID #, but it is a different data set. I want to filter the ID #'s the same for the two sheets, but how do I do that being that I am working with two different sets of data?
Solved by V. H. in 22 mins
hi, my vlookup wont work in some rows but does in others. its temperemental. e.g it will work on rows 10 to 15, then not row 16 and 17, but the will for the rest.
Solved by K. Q. in 21 mins
I need to find out if the 2018 yearly sales goals were met if the yearly sales were $25,000 or more using an IF logical function and to set the formula to return a value of YES if met, and NO if not
Solved by A. A. in 18 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc