Multiple If Statement on a Vlookup

[bspence]

Hello, need help and struggling with a multiple If statement on a vlookup

=IF(VLOOKUP(A7,'Stock Planning - All Orders on MOM By Line Not Shipped.csv'!$C:$F,4,0)="BO","No","Yes")

I want to also include if the vale in the lookup is PB as well as BO,

How do i do this?

 

[JackDanIce]

Welcome to the board.Try:

=IF(IFERROR(FIND(VLOOKUP(A7,'Stock Planning - All Orders on MOM By Line Not Shipped.cvs'!$C:$F,4,0),"BOPB"),0)>0,"No","Yes")

Alternatively, you could replace string values of "No" and "Yes" with Boolean values, (FALSE or TRUE) with:

IFERROR(FIND(VLOOKUP(A7,'Stock Planning - All Orders on MOM By Line Not Shipped.cvs'!$C:$F,4,0),"BOPB"),0)=0

 

[helpexce]

=IF(OR(VLOOKUP(A7,'Stock Planning - All Orders on MOM By Line Not Shipped.csv'!$C:$F,4,0)="BO",VLOOKUP(A7,'Stock Planning - All Orders on MOM By Line Not Shipped.csv'!$C:$F,4,0)="PB"),"No","Yes"

Please try above formula.

 

[bspence]

Thanks for the replies!

@Jackdanice

The code does not seem to work, i want it to return Yes if it finds either BO or PB seperately in the vlookup cell, any advice?

Can it do this to a CSV instead of an xls?

 

[bspence]

helpexce yours does not seem to work

Any other ideas?

 

[JackDanIce]

Hello, need help and struggling with a multiple If statement on a vlookup

=IF(VLOOKUP(A7,'Stock Planning - All Orders on MOM By Line Not Shipped.csv'!$C:$F,4,0)="BO","No","Yes")

Try:

=IF(IFERROR(FIND(VLOOKUP(A7,'Stock Planning - All Orders on MOM By Line Not Shipped.cvs'!$C:$F,4,0),"BOPB"),0)>0,"Yes","No")

 

[bspence]

Hey

But it wont find any BOPB

I want it to come back with no if it find a PB OR a BO on the vlookup, 2 seperate terms.

Any idea?

 

[bspence]

jackdanice

Think it needs some Or in it for both? I want it to give me a result if it finds BO or PB, not together, how do i do that?

 

[JackDanIce]

This part does that:

FIND(VLOOKUP(A7,'Stock Planning - All Orders on MOM By Line Not Shipped.cvs'!$C:$F,4,0),"BOPB")

By finding the VLOOKUP term in the string "BOPB", i.e. if "BO" matches any part of "BOPB" or "PB" matches any part of "BOPB", returning the position number of the match (1 or 3).It returns 0 if no match with the IFERROR wrapped around it, then you test if the value is greater than 0 ("Yes") or not ("No").

It works on filling cells A1:A3 with "BO", "PB", "AB" and running that part of the formula against it returning values of "Yes", "Yes" and "No" respectively.

 

[Special-K99]

Try this

=IF(SUM(COUNTIFS(C1:C1000,A7,F1:F1000,{"PB","BO"}))<>0,"Pass","Fail")
This will return Pass for an existence of PB or BO or both in that column

=IF(AND(SUM(COUNTIFS(C1:C1000,A7,F1:F1000,{"PB","BO"}))<>0,
COUNTIFS(C1:C1000,A7,F1:F1000,"PB")*COUNTIFS(C1:C1000,A7,F1:F1000,"BO"))=0,"Pass","Fail")
This will return Pass for an existence of PB but no BO
or BO but no PB in that column