Forum Discussion
If Statement with V look up
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.
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
- condition remains whatever it is now
- D3 contains either ARRVW or APCRD (that could be different to accord with your sheet's design)
- array is the table to which your current VLOOKUP refers
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.
=IF(X2="APRVW:Firmwide:Firmwide",VLOOKUP(MID(J2,1,1),'DoNotDelete_AP Assignments'!$A:$B,2,FALSE),"Not with AP")
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.
- (You don't need to reply to each of us separately. We all see all the messages in the string. So any of your replies can take into account all that either of us has asked.)