if and or help needed

[rogihead]

I am in the process of creating formulas that I need to then join them all together as one larger formula. I need to check cells to see if they meet some criteria and if so then do a Vlookup. I have successfully done some of the formula but I ALWAYS seem to get hung up on the if, and , or syntax and it is frustrating. So I have

=IF(AND(K1>=9000,K1<=9005,N1="BX"),VLOOKUP(M1,NR1,7,FALSE),VLOOKUP(M1,NR1,6,FALSE))
which will check to see if the value of K1 id between 9001-9005 and if N1 is equal to BX. This seems to work fine.

The issue I am having is that I also need to add another criteria for the value of K1. For instance, if the value of K1 is between 8001 and 8005 or 9001 and 9005 and if N1 is BX. Also if the value of K1 is between 1 and 4999 or if K1 is 9999 and N1 is BX. It seems to be that the OR portion of the equation, and I have tried it a few ways, is where I run into problems. I either get a syntax error I get a false for for the cell.

This is one of the formulas I am putting together and each of the 3 will do a Vlookup on a different named range so my next part of this, once I get all of the smaller components to work is to put those together to figure out the value of the cell I need. To Clarify that in English:

If K1 is 8000 - 8005 or 9001 - 9005 and N1=BX, then Vlookup M1 in NAMED RANGE 1, column 7
If K1 is 1 - 4999 or 9999 and N1=BX, then Vlookup M1 in NAMED RANGE 2, column 7
If K1 is 5000 - 7999 or 8006 - 9000 or 9999, then Vlookup M1 in NAMED RANGE 2, column 7
Otherwise return "No Value Found"

I always try to figure it out before posting but I think I may have hit the wall.

 

[jaylobo]

Here is my best guess:

=IF(OR(AND(K1>=8000,K1<=8005),AND(K1>=9001,K1<=9005)),***Enter VLookup Here***,IF(OR(AND(K1>=1,K1<=4999),AND(K1=9999, N1="BX")), ***VLOOKUP***,IF...

Follow this method for the third constraint and the final false with "No Value Found" and make sure you add in all of the parentheses at the end!

Hope this helps!

 

[rogihead]

I tried to type this post quickly before lunch and I forgot a few things. Ultimately it should be

If K1 is 8000 - 8005 or 9001 - 9005 and N1=BX, then Vlookup M1 in NAMED RANGE 1, column 7, OTHERWISE Vlookup M1 in NAMED RANGE 1, column 6
If K1 is 1 - 4999 or 9999 and N1=BX, then Vlookup M1 in NAMED RANGE 2, column 7, OTHERWISE Vlookup M1 in NAMED RANGE 2, column 6
If K1 is 5000 - 7999 or 8006 - 9000 or 9999, then Vlookup M1 in NAMED RANGE 3, column 7, OTHERWISE Vlookup M1 in NAMED RANGE 3, column 6
Otherwise return "No Value Found"

 

[thisoldman]

I could, I think, figure out when you want to search in column 7 of your three search ranges. I cannot figure out when you want to search column 6.

This is the logic I have so far. It's likely to be incorrect.

' When N1 equals "BX"
If N1 =  "BX" And K1 >= 8000 And K1 <= 8005 Then VLOOKUP(M1, Range1, 7, 0)
If N1 =  "BX" And K1 >= 9001 And K1 <= 9005 Then VLOOKUP(M1, Range1, 7, 0)
If N1 =  "BX" And K1 >= 1    And K1 <= 4999 Then VLOOKUP(M1, Range2, 7, 0)
If N1 =  "BX" And K1 =  9999                Then VLOOKUP(M1, Range2, 7, 0)

' When N1 does not equal "BX"
If N1 <> "BX" And K1 =  9999                Then VLOOKUP(M1, Range3, 7, 0)

' When the value of N1 does not matter
If                K1 >= 5000 And K1 <= 7999 Then VLOOKUP(M1, Range3, 7, 0)
If                K1 >= 8006 And K1 <= 9000 Then VLOOKUP(M1, Range3, 7, 0)

Could you state the conditions that are required to be true to search column 6 instead of column 7.

And when do you want "No Value Found", perhaps when the VLOOKUPs fail?

 

[FDibbins]

I am in the process of creating formulas that I need to then join them all together as one larger formula.

Sometimes, it is better to have a few helper columns with those smaller parts in, rather than 1 huge formula. This can make trouble-shooting/editing easier and more simple to do

 

[FDibbins]

Reading post 1 again, I get the feeling that a table with the different "sets" in, and using INDEX/MATCH might work for you here.

Quick questions...

If K1 is 8000 - 8005 or 9001 - 9005 and N1=BX

What if K1 is not within that range - or within the other ranges, and N1=BX?
what if K1 IS within that range and B1 <> BX?
What if K1 is not within that range - or within the other ranges, and N1<>BX?

 

[rogihead]

Reading post 1 again, I get the feeling that a table with the different "sets" in, and using INDEX/MATCH might work for you here.

Quick questions...

What if K1 is not within that range - or within the other ranges, and N1=BX?
what if K1 IS within that range and B1 <> BX?
What if K1 is not within that range - or within the other ranges, and N1<>BX?

I believe that you may think that BX is a cell, however, "BX" an abbreviation for BOX in this case

 

[rogihead]

I guess that is what I am trying to do first to get that syntax correct for each portion of what I need to accomplish and then, if possible, make one formula to cover all of the cells.

 

[rogihead]

' When N1 equals "BX"
If N1 = "BX" And K1 >= 8000 And K1 <= 8005 Then VLOOKUP(M1, Range1, 7, 0)
If N1 = "BX" And K1 >= 9001 And K1 <= 9005 Then VLOOKUP(M1, Range1, 7, 0)
If N1 = "BX" And K1 >= 1 And K1 <= 4999 Then VLOOKUP(M1, Range2, 7, 0)
If N1 = "BX" And K1 = 9999 Then VLOOKUP(M1, Range2, 7, 0)
If N1 = "BX" And K1 >= 5000 And K1 <= 7999 Then VLOOKUP(M1, Range3, 7, 0)
If N1 = "BX" And K1 >= 8006 And K1 <= 9000 Then VLOOKUP(M1, Range3, 7, 0)


' When N1 does not equal "BX"
If N1 <> "BX" And K1 >= 8000 And K1 <= 8005 Then VLOOKUP(M1, Range1, 6, 0)
If N1 <> "BX" And K1 >= 9001 And K1 <= 9005 Then VLOOKUP(M1, Range1, 6, 0)
If N1 <> "BX" And K1 >= 1 And K1 <= 4999 Then VLOOKUP(M1, Range2, 6, 0)
If N1 <> "BX" And K1 = 9999 Then VLOOKUP(M1, Range2, 6, 0)
If N1 <> "BX" And K1 >= 5000 And K1 <= 7999 Then VLOOKUP(M1, Range3, 6, 0)
If N1 <> "BX" And K1 >= 8006 And K1 <= 9000 Then VLOOKUP(M1, Range3, 6, 0)

Essentially if it is a box we need to pull from column 7 (BOX PRICING) in the named ranges, but if it is any other unit of measure we need to pull from column 6 (UOM PRICING).
I hope that clears it up.

In the 'Burgh area myself

 

[thisoldman]

Try this.

=IFERROR(VLOOKUP(M1,IF(OR(AND(K1>=8000,K1<=8005),AND(K1>=9001,K1<=9005)),Range1,IF(OR(AND(K1>=1,K1<=4999),K1=9999),Range2,IF(OR(AND(K1>=5000,K1<=7999),AND(K1>=8006,K1<=9000)),Range3))),IF(N1="BX",7,6),0),"No Value Found")

The formula is not written in a style I would normally use, so please check it thoroughly.