+ Reply to Thread
Results 1 to 6 of 6

Using LEFT inside INDEX-MATCH to find values that match first 5 letters of referenced cell

  1. #1
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Using LEFT inside INDEX-MATCH to find values that match first 5 letters of referenced cell

    I'm trying to combine a "Vendor" summary sheet and a "Customer" summary sheet. The problem is some companies are on both lists and hence there are times when there is only one entry to move to the "Combined" list, and other times when there are two entries that need to be added and then moved to the list.

    This the unsuccessful formula I have so far in cell C6:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Here's a screen grab of the sheet:
    Combine Data.jpg

    Here's a reduced version of the file (look under "Pallets - Year" tab):
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-04-2004
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    186

    Re: Using LEFT inside INDEX-MATCH to find values that match first 5 letters of referenced

    I see 2 problems
    1.You are doing formula on an array so need to press Control,Shift and Enter rather than just Enter when finishing formula
    2 That will return a #REF error as you have #REF error in V6. Correct the V6 error first and enter formula in c6 as I describe above

  3. #3
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: Using LEFT inside INDEX-MATCH to find values that match first 5 letters of referenced

    Thanks for the input, double checked everything & still no go. I actually get a #N/A error.

  4. #4
    Forum Contributor
    Join Date
    08-04-2004
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    186

    Re: Using LEFT inside INDEX-MATCH to find values that match first 5 letters of referenced

    And you did ctrl+shift+enter and not just enter?
    If i replace V6 with a number it works with no other change to the formula than the above

    Edit:You get #N/A as the item in b6 is NOT found in U6. I had replaced it u6 with contents of B6 to test it.

    What did you want to happen in that case?
    Last edited by philaugust2004; 02-26-2017 at 02:39 PM.

  5. #5
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: Using LEFT inside INDEX-MATCH to find values that match first 5 letters of referenced

    Quote Originally Posted by philaugust2004 View Post
    Edit:You get #N/A as the item in b6 is NOT found in U6. I had replaced it u6 with contents of B6 to test it.
    That's what is happening, the formula can't handle not seeing the value in B6 in either V6 or AN6!!

    Okay now, that means I was on the right path, but need to tweak the formula to handle cases when it doesn't see B6 in both columns....which will always occur. Back to the drawing board.

    While I work on that, do you have any suggestions for that tweak?

    By the way, thanks so much for shining some light on that problem. I was going half insane with this.

  6. #6
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Thumbs up Re: Using LEFT inside INDEX-MATCH to find values that match first 5 letters of referenced

    Actually, I think I have it!
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    EDIT #1: Nope that wasn't quite it. Here's my latest unsuccessful effort:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If anybody has any suggestions, please feel free. Although, you may be enjoying the Excel hack show I'm putting on.


    EDIT #2: I guess third time's a charm. Here's the working formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    By the way, I had to change the LEFT function to 4 characters instead of 5 because I found a name with only 4 letters.
    Now this puppy is solved.
    Last edited by Big.Moe; 02-26-2017 at 05:27 PM.

+ 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. Replies: 5
    Last Post: 02-18-2017, 11:21 AM
  2. [SOLVED] Match a cell value in another sheet and copy values below the cell (Index Match)
    By naga in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-17-2017, 03:14 AM
  3. Using LEFT, MID, or RIGHT within VLOOKUP or INDEX/MATCH/MATCH
    By eszeein2 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-05-2016, 03:38 PM
  4. Replies: 3
    Last Post: 10-28-2015, 09:54 AM
  5. [SOLVED] Exclude Zero's from Min Function nested inside an index+Match... all within CELL funtion
    By clemsoncooz in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-05-2014, 05:16 PM
  6. [SOLVED] Index Match with lookup values containing letters, numbers and spaces
    By makinwaves in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-18-2014, 08:06 PM
  7. Replies: 5
    Last Post: 02-29-2012, 08:51 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1