Comparing and matching data

**atif_ar** · 12-12-2015, 02:32 PM

Dear I have three columns in one sheet and three columns in another sheet ; want to compare both columns and if all three entries are matching gives me the fourth column (date )as result

sheeti sheet2
1 2 3 4 1 2 3
13332545669 14432545669 625691 12-Dec-15 13332545669 14432545669 625691
13332545670 14432545670 525691 13-Dec-15 13332545670 14432545670 525691
13332545671 14432545671 452146 14-Dec-15 13332545671 14432545671 452146
13332545672 14432545672 586233 15-Dec-15 13332545672 14432545672 586233
13332545673 14432545673 265465 16-Dec-15 13332545673 14432545673 265465
13332545674 14432545674 516112 17-Dec-15 13332545674 14432545674 516112
13332545675 14432545675 215135 18-Dec-15 13332545675 14432545675 215135
13332545676 14432545676 114162 19-Dec-15 13332545676 14432545676 114162
13332545677 14432545677 114166 20-Dec-15 13332545677 14432545677 114166
13332545678 14432545678 548465 21-Dec-15 13332545678 14432545678 548465
13332545679 14432545679 165165 22-Dec-15 13332545679 14432545679 165165
13332545680 14432545680 566513 23-Dec-15 13332545680 14432545680 566513
13332545681 14432545681 321155 24-Dec-15 13332545681 14432545681 321155
13332545682 14432545682 235161 25-Dec-15 13332545682 14432545682 235161
13332545683 14432545683 154154 26-Dec-15 13332545683 14432545683 154154
13332545684 14432545684 165165 27-Dec-15 13332545684 14432545684 165165
13332545685 14432545685 212122 28-Dec-15 13332545685 14432545685 212122
13332545686 14432545686 184815 29-Dec-15 13332545686 14432545686 184815
13332545687 14432545687 101154 30-Dec-15 13332545687 14432545687 101154
13332545688 14432545688 203215 31-Dec-15 13332545688 14432545688 203215
13332545689 14432545689 203202 1-Jan-16 13332545689 14432545689 203202
13332545690 14432545690 651616 2-Jan-16 13332545690 14432545690 651616
13332545691 14432545691 201112 3-Jan-16 13332545691 14432545691 201112
13332545692 14432545692 231413 4-Jan-16 13332545692 14432545692 231413

**Glenn Kennedy** · 12-12-2015, 02:44 PM

Was it intentional that EVERY row matched? BtW, next time, please post an Excel sheet. It takes a bit of time to reproduce what you dumped on the screen in an Excel sheet to enable is to test the formulae.

=IFERROR(INDEX(Sheet1!$D$1:$D$24,MATCH(1,INDEX((Sheet1!$A$1:$A$24=$A1)*(Sheet1!$B$1:$B$24=$B1)*(Sheet1!$C$1:$C$24=$C1),0),0)),"")

**atif_ar** · 12-12-2015, 02:56 PM

Dear

Surely next time; can you explain the formula step wise please. You are genius

**Glenn Kennedy** · 12-12-2015, 03:14 PM

The formula returns values from column D of sheet1 based on three criteria. - that the data in columns A, B and C match those in sheet 1. The * between each statement implies AND; ie that column A AND column B AND column C all match. You can follow the individual steps that Excel goes through by going: FORMULAS/FORMULA AUDITING/EVALUATE FORMULA.

The box that opens up is IRRITATINGLY small and cannot be resized, but you can step-by-step follow what it's doing.

So... Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

**atif_ar** · 12-12-2015, 06:59 PM

dear i done solved and reputation. Please 1 by 1 can you explain formula. as 1 number in match function i didn't got and index and match 2 times please help me. I will be very thankful

**atif_ar** · 12-13-2015, 12:45 PM

dear please kindly help me in solving this formula step wise please wanna be expert like u in excel. Kindly help me

**Glenn Kennedy** · 12-13-2015, 01:10 PM

i do not understand what you mean by:

as 1 number in match function i didn't got and index and match 2 times

**atif_ar** · 12-13-2015, 02:02 PM

Dear,

Kindly explain me step wise below function

=IFERROR(INDEX(Sheet1!$D$1:$D$24,MATCH(1,INDEX((Sheet1!$A$1:$A$24=$A1)*(Sheet1!$B$1:$B$24=$B1)*(Sheet1!$C$1:$C$24=$C1),0),0)),"")

**Glenn Kennedy** · 12-13-2015, 02:43 PM

Your three criteria are:
(Sheet1!$A$1:$A$24=$A1)
(Sheet1!$B$1:$B$24=$B1)
(Sheet1!$C$1:$C$24=$C1)

This syntax INDEX((Sheet1!$A$1:$A$24=$A1)*(Sheet1!$B$1:$B$24=$B1)*(Sheet1!$C$1:$C$24=$C1),0) means:
criterion 1 is met AND criterion 2 is met AND criterion 3 is met.

Each criterion returns either TRUE or FALSE. They are multiplied by each other. In doing so, they are coerced into being numbers. Only TRUExTRUExTRUE yields a number, that number being the row number wthin the array where all 3 criteria are met. In all other cases, the value returned is zero.

The MATCH function then returns the row number in the source sheet where all 3 criteria are met. the outer INDEx function: INDEX(Sheet1!$D$1:$D$24 them returns the corrsponding value from column D.

**atif_ar** · 12-14-2015, 01:14 PM

please can i have any example for step wise explanation. Please help me in this completely

**atif_ar** · 12-14-2015, 01:13 PM

Dear you are really nice but your explanation is really complex for me. Dear what is meant by (Sheet1!$A$1:$A$24=$A1) i didn't get

**Glenn Kennedy** · 12-14-2015, 01:34 PM

Have you done as I suggested back at Post 4. Go to FORMULAS/FORMULA AUDITING/EVALUATE FORMULA. There you can see EXACTLY what the formula does, step-by-step. It could take hours to type out a full and detailed explanation. You must be prepared to help yourself!!!

In the case of your specific question:
(Sheet1!$A$1:$A$24=$A1) This means that Excel looks through all the values between A1 and A21 in sheet 1 for a match with A1 on the sheet in which the formula is located. If it finds a a match it returns TRUE, if not it returns FALSE. So the array is a total of 21 values, either TRUE or FALSE.

**atif_ar** · 12-14-2015, 03:40 PM

And what does MATCH(1,INDEX((Sheet1!$A$1 means for example whatdoes 1 mean in match

**Glenn Kennedy** · 12-14-2015, 06:41 PM

Have you done as I suggested back at Post 4. Go to FORMULAS/FORMULA AUDITING/EVALUATE FORMULA. There you can see EXACTLY what the formula does, step-by-step. It could take hours to type out a full and detailed explanation. You must be prepared to help yourself!!!

Comparing and matching data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View