Forum Discussion

Copper Contributor

Sep 14, 2022

Solved

If Statement with V look up

Hello, I have a formula that is working great but i need to add another condition to the if statement. Right now my formula is looking for ARRVW and if it finds it, it will do a vlookup and retur...

excel

Formulas and Functions

mathetes
Sep 14, 2022
psaavedra3

Well, both Harun24HR and I asked for more of the context. The formula as shown makes references that require us to speculate as to what's in J2, among other things.

But it now appears that you are asking how to get the first part of the formula, the IF condition, to respond with a lookup whether or not X2 contains APRVW or APCRD at the start. Is that correct? And it's important to note that your current formula actually is asking whether or not X2 contains "APRVW:Firmwide:Firmwide", not merely "APRVW"

So although the formula could be revised to read
=IF(or(left(X2,5)="APRVW",left(X2,5)="APCRD"),VLOOKUP(MID(J2,1,1),'DoNotDelete_AP Assignments'!$A:$B,2,FALSE),"Not with AP")

HOWEVER, I'm not comfortable recommending that because, as noted in my first reply, you're hardcoding a variable into the formula. And, by definition, variables vary.

PLEASE, therefore, give us a look at the "bigger picture" here. What's the context. How variable are the conditions in the future. Do not engage in hardcoding without an awareness of its risks.

psaavedra3

Copper Contributor

Sep 14, 2022

Hello and thanks for the response. i need the formula to look for both APRVW and APCRD and if either of those values is in the cell im looking at, do the vlookup. my current formula is this:

=IF(X2="APRVW:Firmwide:Firmwide",VLOOKUP(MID(J2,1,1),'DoNotDelete_AP Assignments'!$A:$B,2,FALSE),"Not with AP")

mathetes

Gold Contributor

Sep 14, 2022

psaavedra3

Well, both Harun24HR and I asked for more of the context. The formula as shown makes references that require us to speculate as to what's in J2, among other things.

But it now appears that you are asking how to get the first part of the formula, the IF condition, to respond with a lookup whether or not X2 contains APRVW or APCRD at the start. Is that correct? And it's important to note that your current formula actually is asking whether or not X2 contains "APRVW:Firmwide:Firmwide", not merely "APRVW"

So although the formula could be revised to read

=IF(or(left(X2,5)="APRVW",left(X2,5)="APCRD"),VLOOKUP(MID(J2,1,1),'DoNotDelete_AP Assignments'!$A:$B,2,FALSE),"Not with AP")

HOWEVER, I'm not comfortable recommending that because, as noted in my first reply, you're hardcoding a variable into the formula. And, by definition, variables vary.

PLEASE, therefore, give us a look at the "bigger picture" here. What's the context. How variable are the conditions in the future. Do not engage in hardcoding without an awareness of its risks.