+ Reply to Thread
Results 1 to 5 of 5

Index/Match two arrays

  1. 01-22-2017, 02:53 PM #1
    T86157
    T86157 is offline
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Index/Match two arrays

    Hi,

    I would like to have the resulting column B, look at column A data and match it with wildcards before/after data in column D, then return the indexed value from column E to the cells in column B. I attached an example that might help. Please let me know if this is doable. Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-22-2017, 03:02 PM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Index/Match two arrays

    Enter formula in B2 and copy down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 01-22-2017, 03:42 PM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index/Match two arrays

    Try this...

    Data Range
    A
    B
    1
    Data to look at
    Result
    2
    NTWRK_MN_HVAC_CH-1_SS
    START/STOP
    3
    NTWRK_MN_HVAC_CH-2_FDBK AV
    FEEDBACK
    4
    NTWRK_MN_HVAC_CH-3_SPDx
    SPEED CONTROL


    This formula entered in B2 and copied down:

    =LOOKUP(1E100,SEARCH(D$2:D$9,A2),E$2:E$9)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  4. 01-22-2017, 04:35 PM #4
    T86157
    T86157 is offline
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Re: Index/Match two arrays

    Awesome, thank you guys! Tony, out of curiosity what is the function of the "1E100"?
    Reply With QuoteRegister To Reply
  5. 01-22-2017, 04:46 PM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index/Match two arrays

    Quote Originally Posted by T86157 View Post
    Tony, out of curiosity what is the function of the "1E100"?
    See this for an explanation of how the formula works...

    https://www.excelforum.com/showthread.php?p=4492401
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  2. [SOLVED] Using Max with Index & Match across two arrays of data
    By mike_vr in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-06-2013, 10:34 AM
  3. Index/Match with mulitple arrays
    By Jeannie2006 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-15-2013, 10:56 PM
  4. Sum Index Match Arrays
    By CaesarBob in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-19-2012, 04:38 AM
  5. [SOLVED] Index & Match returning incorrect value. Arrays fixed and exact match used.
    By SDes in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-07-2012, 08:29 PM
  6. referencing arrays in index and match
    By inky in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2007, 07:06 AM
  7. MATCH, INDEX and VLOOKUP with Arrays
    By Dean Hinson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2005, 12:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1