SOLVED

If Statement with V look up

Copper Contributor

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

6 Replies
Post your current formula and sample data. Also mention your version of Excel.

@psaavedra3 

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.

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")
I am using Microsift 365 and my current formula is below.

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

(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.)
best response confirmed by psaavedra3 (Copper Contributor)
Solution

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

1 best response

Accepted Solutions
best response confirmed by psaavedra3 (Copper Contributor)
Solution

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

View solution in original post