+ Reply to Thread
Results 1 to 11 of 11

LEFT function not resolving correctly

  1. #1
    Forum Contributor
    Join Date
    09-19-2007
    Posts
    115

    LEFT function not resolving correctly

    in the following function the LEFT function is not resolving correctly. when i break the individual components out they resolve correctly, yet when i bring them together the LEFT returns a FALSE when it is in fact TRUE. any thoughts?

    =IF(ISNUMBER($B33),MATCH($B33,AcctNum,0),IF((LEFT($C33="P")),MATCH($B33,AcctLine,0),MATCH($B33,ReportLine,0)))

    what the formula is trying to do is:
    1. look at B33 and determine if it is an account number or an account name
    2. if it is an account number, it is to look up a range of account numbers.
    3. if it is an account name (ie not an account number), it is then to look into C33 to determine if the account name comes from a "Profit" or "Report"; it does this by determining if C33 begins with a "P" or not.
    4. depending on whether it's a profit or a report item it then looks up the appropriate range.

    i will include an IF to return a "Valid", "Invalid" result accordingly, yet the LEFT and MATCH is failing to return the reference. at the moment B33 is text and C33 is "Profit".

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,600
    Quote Originally Posted by inky
    in the following function the LEFT function is not resolving correctly. when i break the individual components out they resolve correctly, yet when i bring them together the LEFT returns a FALSE when it is in fact TRUE. any thoughts?

    =IF(ISNUMBER($B33),MATCH($B33,AcctNum,0),IF((LEFT($C33="P")),MATCH($B33,AcctLine,0),MATCH($B33,ReportLine,0)))

    what the formula is trying to do is:
    1. look at B33 and determine if it is an account number or an account name
    2. if it is an account number, it is to look up a range of account numbers.
    3. if it is an account name (ie not an account number), it is then to look into C33 to determine if the account name comes from a "Profit" or "Report"; it does this by determining if C33 begins with a "P" or not.
    4. depending on whether it's a profit or a report item it then looks up the appropriate range.

    i will include an IF to return a "Valid", "Invalid" result accordingly, yet the LEFT and MATCH is failing to return the reference. at the moment B33 is text and C33 is "Profit".
    Hi Inky,
    what do you want to happen, when there is a match?

  3. #3
    Forum Contributor
    Join Date
    09-19-2007
    Posts
    115
    if it's true, ie the line item or account is in the list, i want the cell that contains the formula (let's say D33) to display "Valid". if it's not on the list, i want the cell to display "Invalid".

    i haven't added that IF, but it will encase the current formula.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,600
    Here's a vlookup example that you could try,
    select an item from the drop down menu, to see how the formula changes when you select an item that is not on the list
    Attached Files Attached Files

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    You have a parenthesis in the wrong place. You have

    =LEFT($C33="P")

    this needs to be

    =LEFT($C33)="P"

  6. #6
    Forum Contributor
    Join Date
    09-19-2007
    Posts
    115
    thanks ddl... i guess staring at rows and columns for too long can get to you!

  7. #7
    Forum Contributor
    Join Date
    09-19-2007
    Posts
    115
    hmmm... so ive now encased that formula in an if, yet when the formula doesn't find the account or line it displays "N/A" rather than "Invalid". any suggestions?

    =IF(IF(ISNUMBER($B33),MATCH($B33,AcctNum,0),IF((LEFT( $C33="P")),MATCH($B33,AcctLine,0),MATCH($B33,Repor tLine,0))),"Valid","Invalid")

    these "simple" formulas are scarring and confusing me :o(

  8. #8
    Forum Contributor
    Join Date
    09-19-2007
    Posts
    115
    got it - used an ISERROR! yet i'm now curious as to whether there's another way?

  9. #9
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127
    Your formula is kinda wacky. If you post a .zip sample of your file, it will be easier to fix it for you.

    I'll give it a shot:

    Please Login or Register  to view this content.
    1. You do not need two IFs to start it off. =IF(ISNUMBER($B33) should suffice
    2. LEFT($C33="P") should be LEFT($C33)="P"
    3. You have too many conditional "answers". Here is the progression of the formula:
    a. If B33 is a number, then match B33 in AcctNum. If not, then IF the first letter of C33 is a P, match B33 with Acctline. If C33 is not a P, then match B33 with reportLine. I'm not sure how invalid and valid play into the equation.

    Again, post a .zip sample, and I'm sure we can figure it out.

  10. #10
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,600
    Quote Originally Posted by inky
    got it - used an ISERROR! yet i'm now curious as to whether there's another way?
    there's always another way,
    if it works why worry about another way?

  11. #11
    Forum Contributor
    Join Date
    09-19-2007
    Posts
    115
    to learn! and to see if there's a more efficient way.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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