# Thread: Find number appearing in three different columns including a lookup

1. ## 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

2. 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

3. 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. 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. 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. 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.

=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. 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

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

myers218 (2016-08-24)

11. 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. 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

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

myers218 (2016-09-01)

14. 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. 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. Absolutely Fabulous! Thank you Zeddy. Thanks all. I always appreciate your assistance.

18. 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. 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
•