Can anyone help on amending a formula

[ghrek]

Hi

I have this formula and I need to amend it a bit if poss but dont know where to start.

=IF(E66=IFNA(VLOOKUP(A66,'TABLEAU '!A:E,5,FALSE),0),"OK",IF(E66=IFNA(VLOOKUP(A66,'Shere Previous Rec'!A:E,5,FALSE),0),"Previous","NO_MATCH"))

What im trying to do is delete where it looks in Shere Previous Rec for data and I need it instead to look in sheet called "Watford data" and of where the formula is based and look up for duplicates. If there a duplicated entry I need it to say "DUPLICATED" as well as compare against sheet called "TABLEAU" .

Any Ideas?

Thanks

 

[BlueHornet]

Well, the first thing that I'll point out is that you've entered the lookup to Tableau as "... 'TABLEAU '!... with a space after the word. Unless the worksheet name is Tableau-with-a-space then you can omit the single-quotes and definitely delete the added space. (The single quote marks are necessary when the worksheet name contains a space, otherwise not.)

Aside from that ... I'm going to have to think about this some more.

 

[BlueHornet]

Okay, this formula confused me so much that I had to experiment with it to try to make sense of it (and I can't, really), but I might have a partial alternative, and a further suggestion.

At E66 you could use a formula such as this to examine the contents of Tableau and Watford Data to see if the lookup conditions fail, as the formula suggests. You can look up in Tableau first, and if there's no match to the first lookup (the ISNA is true), return a zero and exit the formula. If there IS a matching value (the ISNA is false), then you can look in Watford Data in the same manner, to return a zero (if there's no match to the lookup value and the ISNA is true) or "something else" which isn't clear to me.

This formula works at E66 to perform the two lookups in series (assuming the first lookup results in the NA condition).
=IF( ISNA( VLOOKUP( A66, Tableau!A:E, 5, FALSE)), 0, IF( ISNA( VLOOKUP( A66, 'Watford Data'!A:E, 5, FALSE)), 0, "something else?"))

I don't believe you'll find any way of working through an IF statement to handle all the conditions that you're examining for:
- the possibility of finding no match to the Vlookup in Tableau AND
- the possibility of finding no match to the Vlookup in Watford Data AND
- the possibility of finding duplicates to the lookup value in both of those two sheets.

I have no doubt that you can get where you're trying to go (maybe not on this road), but it's not at all clear to me where you're trying to go. (I never did make sense of the "Previous" entry in the formula.)

Generally, when I'm concerned about potential duplication between two worksheets then I'll set up a helper column on either (or both) of the sheets to see where the duplicate entry might be, and then either filter on the dups or use the duplicate information in a formula somewhere. And I've certainly used formulas as suggested above to see if Vlookups will fail, and respond accordingly. I have never found a way to do both at the same time and in one formula. I'd love to learn otherwise, but I also require formulas that I can understand and audit, too.

 

[ghrek]

It’s an old formula that I’m trying to amend.

In sheet called tableau there is data and there should be the same data in the sheet called Watford. What I’m trying to do is in the sheet called tableau if the data is missing from the sheet called Watford I need it marked as MISSING on next free column cell and in the sheet called Watford if anything is duplicated on that sheet I need it marked as DUPLICATED but the data for it to be duplicated must be an exact match in the row in columns A-D

Does that make it easier?