+ Reply to Thread
Results 1 to 9 of 9

Need "TRUE" VLOOKUP to be more specific

  1. #1
    Registered User
    Join Date
    08-16-2012
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    14

    Need "TRUE" VLOOKUP to be more specific

    I can't use "FALSE" as the source document and formula location are formatted slightly different. I'm dealing with account names. So for instance source document contains Deloitte with a number at the end. The formulated area also contains Deloitte but without the numbers on the end of it. This makes using "FALSE" seemingly impossible and I get N/A as it obviously cannot find a direct match. Using TRUE works in most situations but it seems if there is another account name that shares 2 letters it will always "see" and return results for that one first. Example: my results for Bloomberg are really that of Blackrock (just above it in alphabetical order) and for Deloitte I'm getting Dechart, so again, the same issue. Is there a way to make the VLOOKUP slightly more specific, without requesting an exact match "FALSE"?

    Many, many thanks in advance.
    Jeremy

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Need "TRUE" VLOOKUP to be more specific

    There are ways to work out a partial match w/o using Vlookup. Can you post an example?


    -Edit- just noticed the clock! it's quittin' time.


    Here's an example using an array formula (see attachment). Instead of Vlookup, I need to make an array function return to me the first matching row:

    {=MIN(IF(NOT(ISERROR(FIND(H$4,D:D))),ROW(D:D),""))}

    See attachment. You can use something like that
    Attached Files Attached Files
    Last edited by GeneralDisarray; 07-06-2016 at 04:58 PM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    08-16-2012
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Need "TRUE" VLOOKUP to be more specific

    Thank you. I think we are on the right track. I provided an attachment with more specifics on what the data would look like.

    Thank again,
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,023

    Re: Need "TRUE" VLOOKUP to be more specific

    Using TRUE works in most situations
    That's because when you use TRUE, excel expects a sorted list - and when it cannot find a match, it will return the next lowest "match"
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,023

    Re: Need "TRUE" VLOOKUP to be more specific

    OK, try this...
    =INDEX($J$6:$K$11,MATCH(LEFT(IF(C6="",C5,C6),FIND("/",IF(C6="",C5,C6))-1)&"*",$I$6:$I$11,0),MATCH(D6,$J$5:$K$5,0))
    copied down

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2021
    Posts
    6,137

    Re: Need "TRUE" VLOOKUP to be more specific

    Maybe like this ...

    =VLOOKUP(LEFT(LOOKUP("zzz",$C$6:C6),FIND("/",LOOKUP("zzz",$C$6:C6))-1)&"*",$I$6:$K$11,2+(D6=2016),0)

  7. #7
    Registered User
    Join Date
    08-16-2012
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Need "TRUE" VLOOKUP to be more specific

    My apologies. There is no need to do the IF for 2015 and 2016. I just tried a basic index/match formula but still having the same issues as the VLOOKUP because some of the names begin the same. I've attached an updated example.

    Many thanks for all of the help.
    Attached Files Attached Files

  8. #8
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Need "TRUE" VLOOKUP to be more specific

    Try this (attached).

    Please Login or Register  to view this content.
    That works, but I also tried this:


    =INDEX(L$6:L$11,MATCH($C6,$K$6:$K$11,1)+1)

    That gives bad results when a match is not found - Your 'BLOOMBERG/GLOBAL/GP' line is not found in any of the names. Should that be 'BLOOMBERG FINANCIAL MARKETS/GLOBAL/GP'?
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    16,100

    Re: Need "TRUE" VLOOKUP to be more specific

    One suggestion that I have not seen suggested is the use of "wildcards" when the 4th argument is false. When the 4th argument is false, the lookup value can include wildcard characters. For example =VLOOKUP("Bloomberg*",$I$6:$K$11,2,FALSE) will find the result for the first entry in the table that starts with "Bloomberg". I tried something like =VLOOKUP(LEFT($C6,8)&"*",$I$6:$K$11,2,FALSE) (which assumes that the first 8 characters of the strings in column C will be sufficiently unique for accurate lookup), and it seemed to work correctly.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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] Type the text "TRUE" for specific conditions
    By YasserKhalil in forum Excel General
    Replies: 4
    Last Post: 09-14-2015, 09:12 AM
  2. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. [SOLVED] Conditional Formatting a cell from a "true/false" VLOOKUP
    By Solidstan in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-12-2013, 10:54 AM
  4. VLookup to return "true" or "false"
    By Sph01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-29-2011, 11:10 AM
  5. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  6. Replies: 7
    Last Post: 05-13-2006, 05:02 PM
  7. IF(VLOOKUP("MYDATA", MYNAME, 4) = 0, "TRUE", "FALSE")
    By Souris in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2005, 01:05 AM

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