I have 4 columns that I need to compare to produce an output into a 5th column exampled below:
- IF col1 = "Partner Opportunity" THEN
- AND IF col2 <> "CLM Driven Lead" THEN
- VLOOKUP(col3,col4:col4,1,FALSE)
- RETURN IF MATCH "CLM Driven Lead" ELSE RETURN ""
| col1 | col2 | col3 | col4 | col5 |
|---|---|---|---|---|
| Partner Opportunity | CLM Driven Lead | Pete | Frank | |
| Partner Opportunity | Mary | David | CLM Driven Lead | |
| Frank | Mary |
So far I have tried a combination of different IFs, ORs, ANDs, VLOOKUPs etc. Below are the one's I feel I came the closest to what I am trying to achieve:
=IF(AY2="Partner Opportunity",IF(VLOOKUP(D2,BH:BH,1)=D2,"Update Lead Source to CLM Driven Lead",""),"")
=IF(AND(AY229="Partner Opportunity",R229<>"CLM Driven Lead"),IF(OR(ISNUMBER(MATCH(B229,BH:BH,0)))),"Change Lead Source to CLM Driven Lead","NO")
Any help is appreciated!
CodePudding user response：
Standard IF(AND())
=IF(AND(A2="Partner Opportunity",B2<>"CLM Driven Lead",ISNUMBER(MATCH(C2,D:D,0)))," CLM Driven Lead","")
MATCH will return a number if the match exists and an error if not. The ISNUMBER returns TRUE/FALSE.
