+ Reply to Thread
Results 1 to 5 of 5

Lookup of array values

  1. #1
    Registered User
    Join Date
    08-21-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Lookup of array values

    Hi there, I hope you are all safe and well amidst this weird year of 2020. I have a particular issue on Excel I am struggling with.

    I have a list of product codes, 21,000 to be exact and i have labelled these as products_array.

    I need to check a list of 100,000+ filenames if their filename partially matches the full code in products_array.

    For instance, the products_array list has codes like this.

    AB123456789
    AB123456780

    The filenames are something like as follows.

    Image_AB123456789_098.jpg
    Image_B4329499994_093229.jpg

    With the above examples, the first image matches one of the codes, so we would flag that while the second image does not, so we are not interested in that.

    In your guys opinions which I value highly, what is the best way of doing this? Logically trying to wrap my head around this is proving to be a bit of a pain.

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Lookup of array values

    Hi,

    You could use the match function with wildcards. For example,

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


    Will return a number if the content of C5 if found within the text of any cell in A1:A10000
    Last edited by sweep; 08-21-2020 at 08:53 AM.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    08-21-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Re: Lookup of array values

    Thanks for your reply. Unfortunately for me I am seeing the #N/A error.

    One of my values for instance is A-WH3108690 and the associated filename is 67132323_A-WH3108690__POST_39L.pdf which is located in A1.

    The formula I have is:

    Please Login or Register  to view this content.
    Is there something I am doing wrong?

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Lookup of array values

    Ah, the match formula would need to be the other way around, for example products_array is in the range C1:C100 and the filenames are in A1:A100,

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


    Effectively you're asking, "Does the text in C1 appear within the value in the range A1:A100?"

  5. #5
    Registered User
    Join Date
    08-21-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Re: Lookup of array values

    Thank you for your help, that makes a lot more sense now and works great.

+ 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. Lookup values in an array
    By graeme27uk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2016, 11:34 AM
  2. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  3. lookup array of values using wildcards
    By skalaima in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-10-2013, 03:01 AM
  4. Replies: 19
    Last Post: 05-09-2012, 03:31 AM
  5. Replies: 3
    Last Post: 11-02-2011, 07:51 AM
  6. Lookup - Not all values in Array
    By barnowl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-28-2007, 01:23 AM
  7. array as lookup values
    By tweety127 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-30-2007, 03:57 PM

Tags for this Thread

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