IF Function with multiple VLOOKUP-CodePudding

I need help as I need to track changes on my file. I actually have 2 tabs. One is the Old data and the other one is for the new data.

I have a total of 6 columns

Column A B C D and E are for my references

Column F is for Concatenate of Columns A to E

Column G is for Remarks

What I need is for the remarks column to have a remark of "CHANGED"if any of the columns in ABCDE changed New data versus the Old data

"No Changed" if the data in Old data tab and New tab data matched

"New" if the data in New data tab cannot be found in Old data tab

Here's what I have done so far:

CONCATENATE is the column name in the New data tab

=IF(VLOOKUP([@CONCATENATE],'Old data'!F:F,1,0)=[@CONCATENATE], "NO CHANGE", "CHANGED")

Whenever I make changes, N/A is showing as the result

The progress so far is now okay but just need the New remarks for the newly added data.

If the data is neither changed or no changed, meaning not existing in either old tab or new tab, it should return a New value

=ifna(if(iserror(vlookup([@concatenate],'Old data'!G:G,1,0)),"Changed","No Changed"),"NEW")

CodePudding user response：

As stated in the comments VLOOKUP returns an error if the value is not found. And if the criteria in an IF returns an error it will error.

So use ISERROR() to return TRUE/FALSE to the IF:

=IF(ISERROR(VLOOKUP([@CONCATENATE],'Old data'!G:G,1,0)),"CHANGED","NO CHANGE")

Another method that may be a little quicker is to use MATCH:

=IF(ISERROR(MATCH([@CONCATENATE],'Old data'!G:G,0)),"CHANGED","NO CHANGE")

This is my preferred method.

CodePudding user response：

Building off what the others here have answered, it sounds like it should be something like:

=IF(ISERROR(MATCH([@Concatenate],'Old data'!F:F,0)),"NEW", IF([@Concatenate]=INDEX('Old data'!F:F,ROW([@Concatenate])),"NO CHANGE", "CHANGED"))

Its a bit ambiguous to determine the exact difference between New and Changed in your question. I have assumed that "CHANGED" means that the corresponding row in new data is different "Old data", but the value in new data can be found elsewhere in column F in old data.

On the other hand, new would be if the value has changed between old and new data but the new value cannot be found within the entirity of column F of the Old data sheet...