If/Then Formula with Multiple Conditions

Hey, i don't really know the best way to explain this so i've created a file to help show what im trying to ask.

\1

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

First problem is that in column D, your VLOOKUP should have FALSE or zero instead of TRUE; this will force an exact match, otherwise it returns the value for first match that is equal or greater than your lookup value.

And newer versions of excel will automatically extend formulas for you... so if you enter a new value in column C then columns D & E will automatically extend.

And I personally would not put to separate sets of data directly next to each other like that... I'd suggest at least one empty column between them. This way, if you try to sort one set or the other then it won't treat the two sets of data as one larger data set.

Hi cDuck28Z;

I don't have the time to look at it now, but I'll be back in about 4 hours and can look at it then.
In the meantime here's a suggestion:

Break the formula up into several cells, so that each cell does ONLY 1 calculation.
Some cells might have "If(A3,B3,C3)" or similar.
Then you can easily see where the problem is.

After correcting the problem, you can put the complex formula back together 1 simple cell at a time.

If that doesn't make sense, then when I get back, I'll do it in your workbook so you can see what I mean.

Threads merged. cDuck28Z, you seriously need to read the forum rules before posting again.

Hi cDuck28Z;

So here's how I solve complex formulas.
I break them down into separate cells with each cell having just one function:
Your formula in D9

=IF(C9="","",IF(VLOOKUP(VLOOKUP(LEFT(C9,FIND("-",C9)-1)&"*",A:A,1,0),A:B,2,TRUE)=4,"Yes",""))

I break it down like so:

F9 : =FIND("-",C9)                '4
G9 : =LEFT(C9,F9-1)               'XXP
H9 : =VLOOKUP(G9&"*",A:A,1,0)     'XXP-A
I9 : =VLOOKUP(H9,A:B,2,TRUE)      '1
J9 : =IF(I9=4,"Yes","")           '""
D9 : =IF(C9="","",J9)             '""

With it broken down like this I can see that I9 (=VLOOKUP(H9,A:B,2,TRUE)) is producing the wrong result. I start testing by changing all the "1"s to "2" in column B until I find the one that I9 is finding, and I discover that I9 is finding Row 16 "XXLA-1"

Once I fix the formula in I9 then I start putting the formula back together.
D9 : =IF(C9="","",J9)
1) In D9, replace "J9" with the formula from J9
I literally do a cut & paste. I copy the contents of J9 (except the "="), select D9, highlight "J9" and paste. so I now have:
D9 : =If(C9="","",IF(I9=4,"Yes",""))

2) Replace "I9" with the formula from I9
D9: =If(C9="","",IF(VLOOKUP(H9,A:B,2,TRUE)=4,"Yes",""))

etc.