Sep 14 2022 09:11 AM
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 return a value. I need it to start looking another value APCRD. I need the formula to look for both and either one of those is true do a vlookup and return a value. Can anyone help me? below is my existing form
Sep 14 2022 09:31 AM
Sep 14 2022 09:35 AM
You wrote: below is my existing form
But there was no existing form attached.
That said, not knowing how your spreadsheet and data are arrayed, it might make more sense for the IF formula to read something like
=IF(condition,VLOOKUP(D3,array,2,0)
where
Doing it this way gives more flexibility for future additions to the VLOOKUP table. It avoids "hardcoding" the item that VLOOKUP is using as its source. In general, the avoidance of hardcoding of variables is a good thing.
If that doesn't make sense, please come back and give us more of the context of your inquiry.
Sep 14 2022 09:39 AM
Sep 14 2022 09:48 AM
Sep 14 2022 09:50 AM
Sep 14 2022 09:56 AM - edited Sep 14 2022 09:58 AM
Solution
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.
Sep 14 2022 09:56 AM - edited Sep 14 2022 09:58 AM
Solution
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.