Hi There,
I'm trying to compare 2 worksheets with 100+ columns and 1500+ rows and each worksheet have the same columns but the data changes from week to week (i.e. previous week vs current week). I'm using the IF statement with vlookup formula which works for most cells but others have errors. The data in the cells are numeric, blanks, memo field (definition), alphanumeric, alpha, etc.
I want the formula to do the following compare:
1) if previous week = blank and current week = abc then result = Mismatch
2) if previous week = abc and current week = blank then result = Mismatch
3) if previous week = abc and current week = abc then result = abc
4) if previous week = def and current week = abc then result = Mismatch
5) if previous week = [memo field for definition] and current week = [memo field for definition] then result = [memo field for definition]
=IF(OR(ISBLANK(VLOOKUP(CurrentDD!I2,PreviousDD!I:I,0)),ISNA(VLOOKUP(CurrentDD!I2,PreviousDD!I:I,1,FALSE))),"",VLOOKUP(CurrentDD!I2,PreviousDD!I:I,1,FALSE))
The formula doesn't work when comparing a blank cell vs populated cell as the result is always blank and the memo field gives an error #value. I tried adding another criteria for the Mismatch but no luck. Can you please help me with the correct formula? I've been at this for 3 days now and am very frustrated so any help would be much appreciated.
Thank you so much in advance.
Bookmarks