+ Reply to Thread
Results 1 to 7 of 7

Creating a lookup based on an array of partial text

  1. #1
    Registered User
    Join Date
    02-11-2012
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    34

    Creating a lookup based on an array of partial text

    I have a table with a column that is based on a free-form text description. There are thousands of rows, and each description varies, some end with garbage text and numbers for example.

    These cells do contain a keyword that is consistent among each related row, for example:

    ID Description Value
    0 Left bore A33 $42.00
    1 Right bore B2 $100.00
    2 Top edge $12.58
    3 Bottom edge x3 $87.01

    I have another sheet that contains a table such as:

    Key Category
    bore cat-a
    edge cat-b

    I want to search for the key in the second table in each description of the first table, insert the Category in a new column of each of those rows, much like how you would vlookup a value in an array resulting in the following table:

    ID Description Value Category
    0 Left bore A33 $42.00 cat-a
    1 Right bore B2 $100.00 cat-a
    2 Top edge $12.58 cat-b
    3 Bottom edge x3 $87.01 cat-b

    Can anyone guide me here?

    Thank you!
    Attached Files Attached Files
    Last edited by ritmo2k; 05-13-2022 at 10:40 PM.

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,372

    Re: Creating a lookup based on an array of partial text

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    1,897

    Re: Creating a lookup based on an array of partial text

    worksheet or tab name : FinalData

    Cell A2 formula , Drag down and across to C column

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Cell D2 formula , Drag down

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    02-11-2012
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    34

    Re: Creating a lookup based on an array of partial text

    Hi wk9128,
    I am trying to break down the second formula without much luck.

    I would be extremely grateful for a quick explanation to help me understand.

    Thank you very much for the quick reply.

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    1,897

    Re: Creating a lookup based on an array of partial text

    Please see this attachment
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,542

    Re: Creating a lookup based on an array of partial text

    I think the OP is asking for an explanation of how the second formula works.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  7. #7
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    1,897

    Re: Creating a lookup based on an array of partial text

    Do you want me to explain the usage of the formula? Not my expert? You can try to use the left mouse button to select the formula and range, and press F9 on the keyboard to see the changes

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Lookup based on a partial text string
    By TAllan in forum Excel General
    Replies: 4
    Last Post: 02-24-2022, 01:02 PM
  2. Full & Partial Matches with Lookup Values in Array
    By happy23 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-30-2020, 04:47 AM
  3. SUM an array that contains some text values and partial text strings
    By tv2account in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-04-2020, 09:15 AM
  4. [SOLVED] LOOKUP Value Based on Partial Text available in a Cell
    By Rajeshkumar R in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-21-2017, 07:41 AM
  5. [SOLVED] Find cell in array that contains partial text and use column # of cell to lookup value
    By chuckyfang in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-27-2016, 10:19 AM
  6. Partial lookup from text string, return value from lookup list
    By TotallyLost in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-13-2016, 12:14 PM
  7. Replies: 4
    Last Post: 07-27-2013, 07:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1