Excel Formula Help... yes, me again..

[Slinky]

'ola.. Me again..

Having stress with a different formula this time..

=IF(A2=IF(ISNA(VLOOKUP(A2,OGI_EXCEPTION!A:A,1,FALSE)),(A2=IF(ISNA(VLOOKUP(A2,OGI_Ready!A:A,1,FALSE)),"No",VLOOKUP(A2,OGI_Ready!A:A,1,FALSE)),"Yes","No"))),"Exception"

It'll no doubt be obvious to the Excel gurus that there is a problem here... A virtual pint to anyone that can work out what I've done wrong!!

The formula is supposed to output "Yes", "No" or "Exception" based on the success or failure of 2 vlookups .. but it doesn't!!

I've been looking at it for too long now and can't work out what the hell I've done wrong!

Help!

[anonymous-user]

Can you clarify what your logic test is.

ie.

under what ceircumstances should it return yes, no or exception.

you don't need the insa and you will need to use if(and(.

[Slinky]

OneDs said:

Can you clarify what your logic test is.

ie.

under what ceircumstances should it return yes, no or exception.

you don't need the insa and you will need to use if(and(.

List 1 = List of customer IDs (yes, we're on customer IDs again!)
List 2 = List of customer IDs for which work has been completed
List 3 = List of customer IDs that represent exceptions

lookup entry in list 1 against list 2 to define whether Yes or No is output
lookup entry in list 1 against list 3 to define whether Exception is output

I'm using ISNA to handle the #N/A results from vlookup and provide the No output..

So, theoretically speaking, the formula should check for exceptions (list 3) with VLOOKUP, if the output is #N/A then check list 2 for completed work, if result is #N/A output No, if result is equal to entry in list 1, output Yes ...

Does that make sense?

[Dupont666]

why not:

1. Start by selecting the OldList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:

=COUNTIF(NewList,A2)=0

5. Click the Format button and specify the formatting to apply when the condition is true (a yellow background in this example).
6. Click OK

=====

1. Select the NewList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:

=COUNTIF(OldList,D2)=0

5. Click the Format button and specify the formatting to apply when the condition is true (a green background in this example).
6. Click OK

rinse and repeat for the list 1 -> list 3

Edited by Dupont666 on Tuesday 23 March 11:00

[Slinky]

Dupont666 said:

why not:

1. Start by selecting the OldList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:

=COUNTIF(NewList,A2)=0

5. Click the Format button and specify the formatting to apply when the condition is true (a yellow background in this example).
6. Click OK

=====

1. Select the NewList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:

=COUNTIF(OldList,D2)=0

5. Click the Format button and specify the formatting to apply when the condition is true (a green background in this example).
6. Click OK

Thanks for the assistance, but I need to output the three different values as there are other substantial formulae within the sheet that rely on the output..

I had things working fine until the Exception scenario reared its ugly head and added to the complexity of the IF statements..

[anonymous-user]

just for my sanity are you saying the customer id only appears in list 2 if the work is completed or not completed?

Also if they appear in list 2 can they also appear in list 3?

[Slinky]

OneDs said:

just for my sanity are you saying the customer id only appears in list 2 if the work is completed or not completed?

Also if they appear in list 2 can they also appear in list 3?

1: Customer ID only appears in list 2 if the work is complete
2: Customer ID cannot appear in both list 2 and 3

Also, for info List 2 is on worksheet OGI_READY , List 3 is on worksheet OGI_EXCEPTION

Edited by Slinky on Tuesday 23 March 11:05

[ccr32]

You need to put a TEXT formula in front of your ISNA(s) - when doing if formulae on ISNA output, it won't see the text, and so will always default to the else value.

something like the below should give you the idea...

=IF(A2=IF(TEXT(ISNA(VLOOKUP(A2,OGI_EXCEPTION!A:A,1,FALSE blah blah

[Slinky]

ccr32 said:

You need to put a TEXT formula in front of your ISNA(s) - when doing if formulae on ISNA output, it won't see the text, and so will always default to the else value.

something like the below should give you the idea...

=IF(A2=IF(TEXT(ISNA(VLOOKUP(A2,OGI_EXCEPTION!A:A,1,FALSE blah blah

Really?

This formula :

=IF(A2=IF(ISNA(VLOOKUP(A2,OGI_Ready!A:A,1,FALSE)),"No",VLOOKUP(A2,OGI_Ready!A:A,1,FALSE)),"Yes","No")

works perfectly, it's the addition of the Exception lookup that appears to have broken things.. I'm sure I've just nested the IFs incorrectly, but can't see the wood for the trees!

[ccr32]

To be honest, I couldn't test it out end to end, but when you break the big formula down into its component parts, that was the first thing that failed...

Not to say though that was the only thing that was wrong with it..!

PM me if you'd like me to take a look at the actual sheet

[Dupont666]

=IF(A2=IF(ISNA(VLOOKUP(A2,OGI_Ready!A:A,1,FALSE)),"No",VLOOKUP(A2,OGI_Ready!A:A,1,FALSE)),"Yes","No")

can that not have a IFERROR on it?

=IFERROR(VLOOKUP(), 0)

[Slinky]

Dupont666 said:

=IF(A2=IF(ISNA(VLOOKUP(A2,OGI_Ready!A:A,1,FALSE)),"No",VLOOKUP(A2,OGI_Ready!A:A,1,FALSE)),"Yes","No")

can that not have a IFERROR on it?

=IFERROR(VLOOKUP(), 0)

I'm not questioning that formula, it works!

[Tyre Smoke]

This is like reading one of DeRestrictor's threads.

[anonymous-user]

The if/iserror should do it incase you want the logic of your original process clarified here it is.

First "if(and(" says if the customer ID doesn't appear in either sheet then return "no"

The next "if" says if the customer id doesn't appear in the exceptions sheet then (as it must be in the completed sheet) it returns "yes", as the only option left is that the customer ID must be in the exceptions sheet followingthose two logic tests it then returns "exception".

=IF(AND(ISNA(VLOOKUP($A2,OGI_EXCEPTION!A:A,1,FALSE)),(ISNA(VLOOKUP($A2,OGI_Ready!A:A,1,FALSE)))),"No",IF(ISNA(VLOOKUP($A2,OGI_EXCEPTION!A:A,1,FALSE)),"Yes","Exception"))

Editted yep I over wrote my test ranges with your sheet names and forgot to include the columns references.

Edited by anonymous-user on Tuesday 23 March 13:34

[Slinky]

OneDs, if you were here I'd kiss you!!

That gives exactly the result I was after! I had to make a very minor tweak to add the range references back in for the VLOOKUPS, but it's perfect!!!

PH saves the day!

[anonymous-user]

No probs I had to pop away for a RemCom but enjoy reliving my excel/vba days, keeps my eye in so to speak. No need for a saliva transfer, just spread the karma.