Compare Excel sheets values to update a third value

Question

Example file So I have two sheets that each have lists of part numbers, plant where they come from and two columns on costs. What I need to do is scan them and if Sheet A and Sheet B both have a row with matching part numbers and the plant they come from, then A's two cost values are updated to match B's costs.

The next step is then to highlight all cells in Sheet A that are not on Sheet B and highlight all cells in Sheet B that were copied to Sheet A. I think this last part can be done at the same time the cell is being copied I'm just not sure how to do any of this.

Conditional formatting on countif, and just use lookups, no need for VBA really. So look at formula based conditional formatting and INDEX, MATCH, VLOOKUP etc. — Nathan_Sav
I think it will require a macro because the two sheets may not line up on the same rows so each part number on Sheet A will have to search the column of part numbers in Sheet B and then repeat for the plant made. So it won't be a simple if statement looking at the corresponding row. — Richard Martin
INDEX,MATCH and VLOOKUP do not care if the data is on the same row between the sheets they are made to search the dataset till it finds a match then return a value. And with INDEX and MATCH you can search two columns at once using it as an array formula. If you are returning a number and there is only one match of part number and plant then you can also use SUMIFS(). If you showed some data we could show you how. But Stack Overflow is not a code for me service. So unless you have some vba attempts to show, it is likely this will be down voted and closed. — Scott Craner
I'm starting to understand the index and match way but how would I put the whole column of part numbers and plant locations as the two lookup values? The examples I've found are only for looking up two specific criteria, my values would have to be the part number and plant from one row, do the lookup, then use a new part number and plant as the criteria. — Richard Martin
Show some test data that explains better. It is hard to give you guidance without reference. — Scott Craner

Scott Craner Scott Craner · Accepted Answer · 2016-06-10T17:18:56

This is a formula method.

Because you will not be changing all the values and I assume you want to keep those that do not have a match, then in an empty column next to the figures on sheet 1 put the following formula:

=IFERROR(INDEX(Sheet2!F$3:F$7,MATCH(1,INDEX((Sheet2!$D$3:$D$7=$A3)*(Sheet2!$B$3:$B$7=$C3),),0)),G3)

Then copy over one column and down the the end of the data.

The INDEX((Sheet2!$D$3:$D$7=$A3)*(Sheet2!$B$3:$B$7=$C3),) will create an array of 0 and 1's the same size as the data reference on sheet 2. In this instance it will create a 1 dimensional array that is 5 objects.

The position of these objects of 0 and 1 are relative to the rows. So for the first formula the return array will be {0,1,0,0,0} because only the second row of the data matches both the plant and the part number.

The MATCH(1,INDEX(...),0) then finds the first object in that array that is 1 and returns the relative position, in this case 2 as it is the second in the array.

The Outer INDEX(Sheet2!F$3:F$7,...) then returns the value in the range Sheet2!F$3:F$7 whose relative position is equal to the 2 passed from the MATCH(). So Sheet2!F4.

If no MATCH is found then the whole thing will throw a #N/A error so we capture that error with IFERROR(...,G3) and tell the formula to return the value in column G instead.

This will give you all the proper values:

Then you can copy and paste just the values back to the original spots and hide the columns with the formulas:

Sheet2 for reference:

If you want vba to do the last part of copy and past and hiding then use the macro recorder and then clean up the code.

Compare Excel sheets values to update a third value

1 Answers