# Help needed on columns comparison... thanks!!

#### shamnath

##### New Member
I have two identically formatted spreadsheets with the same record labels on the first row. I need to find which entires from one s/s are missing from the other s/s. I current use this in a third new worksheet:

=IF(ISERROR(VLOOKUP(GMI!D2,PROD!\$D\$2:\$D\$133,1,FALSE)),"","Match")

where GMI and PROD are the two s/s I am comparing. The problem with this is that I am only using ONE record label to compare. How can I add additional labels in the comparison... like I was to also compre E2 (GMI) to \$E\$2:\$E133 (PROD) and F2 (GMI) to \$F\$2:\$F133 (PROD) before it can say "Match".

If this is too complicated because I have failed to explain it well, here is something else:!!

If I am comparing 2 columns (A and B)... and I have the above function to compare each row of A to the entire B. So for every record in A, I have a corresponding column that says "Match" if that record exists in B... how can I get excel to display WHERE in B it found the entry?

I hope someone can help me with this.... thanks soo much. Cheers.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### sunnyland

##### Well-known Member
Hello,
================
To compare the 2 columns
=================
You can use match that will return the position in the array that will work if you only have 1 instance of each date if not it will only return the first one found.
ie:
=MATCH(B5,Sheet1!A2:A6,0) will return 1 for example is the search value is in cell A2

to return the actual row number try:

=MATCH(B5,Sheet1!A2:A6,0)+(ROW(Sheet1!A2)-1)
............................
meaning you find the position in the array then add the row number of the first cell in the array -1 to get the number of offset form first row.
...........................

If you have more than one instance you can try this approach, it will work if you have a maximum of 255 occurences of one date.

1) select your columns of date and filter somewhere else using special filters and unique records only

2) this single list of date will be the base for the formula shown as sample below:
Book1
ABCDEFGHIJ
1Date123456789
223/09/2006200000000
327/09/2006430000000
41/10/2006500000000
56/10/2006860000000
61/01/2005000000000
Sheet1

the top row 1 , 2, 3 ,.....255 is the number of time a date as appeared.
The formula in B2 is the master formula and has to be entered as an array formula to return the correct result
meaning [if you don't know]
typing the formula as:
=LARGE(((mydates=Sheet1!\$A2)*ROW(mydates)),B\$1)

....mydates is just a named range.
I add source and formula on 2 sheets but you can have them on the same sheet :
=LARGE(((\$C\$14:\$C\$18=Sheet1!\$A2)*ROW(\$C\$14:\$C\$18)),B\$1)
.................................................
Think to put \$ to your references array if not when you drag left and down later it will be incorrect.
.........................................
MOST IMPORTANT
Still in the formula bar press:
Ctrl+Shift+Enter
This will give the {} as in the sample from there you can drag to the left and then down without modification to the formula.

Have a go if this suits you

Replies
12
Views
454
Replies
0
Views
97
Replies
0
Views
548
Replies
8
Views
160
Replies
4
Views
246

1,171,585
Messages
5,876,314
Members
433,193
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back