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.

 

[rogihead]

thisold man you are the man!!! I would not have come up with this solution.

I just tried out your solution and the spot checks I did gives ,e the expected values. When going over this with the powers that be, they gave me an additional value of K1 that must be accounted for and that is if K1=9998 should pull its info from range3 which is COT. So this part of the equation in RED I am going to try and edit so that I can add in K1=9998 today.

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

If I am unsuccessful by days end I'll repost what I have and see if you have a possible solution. Thanks again!!!!