Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Aug 2016
    Location
    PA
    Posts
    8
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Find number appearing in three different columns including a lookup

    I need to check three different columns for a Project Number and return the first occurrence only. The third column to check is a lookup. A complete description is in the attached example. Logically, I get it, but just can't get it together.
    Any assistance, thanks!

    Meleia
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Meleia,

    Welcome to the Lounge as a new poster!

    I hope I understood your logic? I did change a couple of entries in your Table (highlighted in yellow) to test all the conditions as I understood them.

    Here's the User Defined Function (UDF) I used:
    Code:
    Option Explicit
    
    Function FindPrj() As Variant
    
    
       Dim lCurRow As Long
       
       Application.Volatile
       
       lCurRow = Application.Caller.Row
       
       If (WorksheetFunction.IsNumber(Cells(lCurRow, "D"))) Then
         FindPrj = Cells(lCurRow, "D")
       Else
          If (Cells(lCurRow, "D")) = "" Then
            If (Cells(lCurRow, "E")) = "" Then
              FindPrj = WorksheetFunction.VLookup(Cells(lCurRow, "B"), Range("OrgCodes"), 2)
            Else
              FindPrj = Cells(lCurRow, "E")
            End If
          Else
            FindPrj = ""
          End If
       End If
          
    End Function  'FindPrj
    Here's the results:
    FindPrj.JPG

    Here's my Test file: Find Project Number from Several Columns RG-V2.xlsm

    Note: I make the Organization lookup range a Named Range "OrgCodes" as it just makes referencing it easier in the code. I also had to convert your Table to a normal Range as the code would cause an Automation Error every time I opened the file! Probably something I'm doing wrong as I don't usually use the Table feature just normal ranges (what us old timers grew up on before the fancy new features ).

    If the logic isn't correct try giving us concrete examples of value a given row in the table should return and from where.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Aug 2016
    Location
    PA
    Posts
    8
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks for the quick response! I've been here for a while under Myers515 and the Lounge is really my go-to place.
    Looking at the example above, I probably used the wrong word "exclude" NO PROJ. What I meant was, if there is NO PROJ, ignore that text (treat as if blank), and go to the next case. It probably sounded like I meant exclude any rows with NO PROJ. Oops!

  4. #4
    New Lounger
    Join Date
    Aug 2016
    Location
    PA
    Posts
    8
    Thanks
    5
    Thanked 0 Times in 0 Posts
    And, not to press my luck, but can you explain a way to do this with a formula and not code, as I have no experience there and would not be able to make changes if the criteria changed.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Does this work in C (and filled down)?

    =IF("NO PROJ"=D2,"",IF(NOT(ISBLANK(D2)),D2,IF(NOT(ISBLANK( E2)),E2,VLOOKUP(B2,OrgCodes,2,FALSE))))

    OrgCodes is a named range as RG suggested.

  6. #6
    New Lounger
    Join Date
    Aug 2016
    Location
    PA
    Posts
    8
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks kweaver.
    This still stops if there is a NO PROJ. It doesn't continue looking in additional columns.
    Lookup works as expected.

    If there is a NO PROJ, I need the formula to go to the next case which would be E2, then the OrgCode table. See my explanation above; don't exclude result, just treat NO PROJ as if it was blank and go to the next case.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    What about this?

    =IFERROR(IF(AND(D2<>"NO PROJ",NOT(ISBLANK(D2))),D2,IF(NOT(ISBLANK(E2)),E2, VLOOKUP(B2,OrgCodes,2,FALSE))),"")

  8. The Following User Says Thank You to kweaver For This Useful Post:

    myers218 (2016-08-24)

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Myers,

    Ok, here's a slightly different approach that fixes the logic and perhaps makes it a little clearer about how you would go about changing it. Personally, I'd rather code a UDF in VBA that workout long nested formulas which are much harder to follow, IMHO. But as always YMMV!

    Code:
    Option Explicit
    
    Function FindPrj() As Variant
    
       Dim lCurRow As Long
    
       Application.Volatile
    
       lCurRow = Application.Caller.Row
    
        Select Case True
        
         Case WorksheetFunction.IsNumber(Cells(lCurRow, "D")) And _
                                         Cells(lCurRow, "D") <> ""
             FindPrj = Cells(lCurRow, "D")
    
         Case (Cells(lCurRow, "D") = "" Or (UCase(Cells(lCurRow, "D"))) = "NO PROJ") And _
               Cells(lCurRow, "E") = "" And Cells(lCurRow, "B") <> ""
              FindPrj = WorksheetFunction.VLookup(Cells(lCurRow, "B"), Range("OrgCodes"), 2)
    
         Case Else
              FindPrj = Cells(lCurRow, "E")
    
    
       End Select
    
    End Function  'FindPrj
    Results:
    FindPrj.JPG

    Test File: Find Project Number from Several Columns RG-V3.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. The Following User Says Thank You to RetiredGeek For This Useful Post:

    myers218 (2016-08-24)

  11. #9
    New Lounger
    Join Date
    Aug 2016
    Location
    PA
    Posts
    8
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks to both of you for the quick responses. I'll use the formula for the sheet that's due right now. Then work the UDF into my permanent file for future use. As always, your help very much appreciated.

    Meleia

  12. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Meleia,

    Here are two additional Backup formulas that will also work:
    In Cell C2:
    Code:
    =IFERROR(IF(D2<>"NO PROJ",D2,IF(ISNUMBER(E2),E2,INDEX(K2:L28,MATCH(B2,K2:K28,0),2))),"")
    
    OR
    
    =IFERROR(IF(MAX(D2:E2)<>0,MAX(D2:E2),INDEX(K2:L28,MATCH(B2,K2:K28,0),2)),"")
    HTH,
    Maud

    meyers.png
    Last edited by Maudibe; 2016-08-27 at 20:22.

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    myers218 (2016-09-01)

  14. #11
    New Lounger
    Join Date
    Aug 2016
    Location
    PA
    Posts
    8
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Maud,
    Sorry for the delay in getting back. Thanks for the additional information. All of the formulas miss one of the scenarios and I'm certain it's my fault in explaining. Here is what I need changed: if D2 is NO PROJ, instead of leaving result blank, continue to next condition. In other words, NO PROJ should not stop the formula.
    Thanks for any assist.
    Meleia

  15. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Meleia

    ..here's my formula:
    =IFERROR(IF(MAX(D2,E2)=0,INDEX(L:L,MATCH(B2,K:K,0) ),MAX(D2,E2)),"")

    zeddy

  16. The Following User Says Thank You to zeddy For This Useful Post:

    myers218 (2016-09-01)

  17. #13
    New Lounger
    Join Date
    Aug 2016
    Location
    PA
    Posts
    8
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Absolutely Fabulous! Thank you Zeddy. Thanks all. I always appreciate your assistance.

  18. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Meleia

    ..and if you would rather have the "NO PROJ" from column [D] returned, instead of a blank, then just use
    =IFERROR(IF(MAX(D2,E2)=0,INDEX(L:L,MATCH(B2,K:K,0) ),MAX(D2,E2)),D2)

    ..it all depends on whether you have other 'text' values for your Project Numbers or not.
    ..if that is the case, another example would be required.

    zeddy

  19. The Following User Says Thank You to zeddy For This Useful Post:

    myers218 (2016-09-11)

  20. #15
    New Lounger
    Join Date
    Aug 2016
    Location
    PA
    Posts
    8
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Zeddy!
    I had a golf instructor who told me he needed five different methods to teach the same principle, to reach all of his students. The Lounge Moderators are definitely excellent instructors! I'm always surprised at the number of ways to accomplish something in Excel.
    Thanks everyone,
    Meleia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •