Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

If Error Match

0

Is it possible on my "Plug" workbook in sheet 2 column H is where i'd need the return of ("Change" or "no Change")

The match in tab "sheet2" would need to be a2 and the value that is in e2:g2 to see if there is a match on tab "Master" b2 and the value that is in "e2:g2"

These would correspond all the way down the worksheet in column H on "sheet2" tab

Thanks so much!

Answer
Discuss

Answers

0
Selected Answer

This question is almost identical to the one you asked the other day. You ought to have been able to construct the formula yourself based on the answer you already had. Here is the formula.

=IF(AND(INDEX(Master!$E:$E,MATCH($A4,Master!$B:$B,0))=$E4,INDEX(Master!$F:$F,MATCH($A4,Master!$B:$B,0))=$F4,INDEX(Master!$G:$G,MATCH($A4,Master!$B:$B,0))=$G4),"","Changed")

However, I think you are constructing your work flow the wrong way. You will need to paste the formula into column H of the imported data sheet every time. Why not have a formula in column L of your Master which checks for changes in "Sheet2". When you change Sheet2 the changes will be highlighted in the Master (you could even use CF to actually highlight them). The formula below will show a result like "Changed (13)" where 13 is the row number in Sheet2 where the change can be found.

=IFERROR(IF(AND(INDEX(Sheet2!$E:$E,MATCH($B3,Sheet2!$A:$A,0))=$E3,INDEX(Sheet2!$F:$F,MATCH($B3,Sheet2!$A:$A,0))=$F3,INDEX(Sheet2!$G:$G,MATCH($B3,Sheet2!$A:$A,0))=$G3),"","Changed ("&MATCH($B3,Sheet2!$A:$A,0)&")"),"")

If the number in B2 is not found in Sheet2 or if it is found and there is no change the cell will remain blank.

If you wish to highlight the changed item use H2<>"" as the criterium, meaning, if H2 <> "" it should be coloured to draw your attention. If you apply the rule to all cells, the entire row could be highlighted depending on the contents of column H.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login