2 files -compare costs for same date and same person ID

[david gabra]

Hi
I have 2 file
In the excel file 1 and file 2
I just want to see where for the same date, there is the same expense in both file.
This analysis should be performed the same Employee ID.
Basically, only the 1st row should give a positive.

 

[srinidhi]

HI David,

A simple vlookup with give you the desired solution.

 

[david gabra]

can you help me, since i don't know how to do with two variables, both empoyeeid, date, and amount, can you give an exampple.

 

[narunfca]

Hi

Enclosed the File. Hope it helps.

with best regards

Arun N

 

[david gabra]

Hi Narunfca,

Please see the attached file.

The formula below works great - but now I see that I need to add an additional factor, to check if the date in file 2 is within the date range in file 1 - and then to return a reply of matched or not.


=IFERROR(IF(F3=INDEX('File 2 '!$C$2:$C$16,MATCH(A3&TEXT(C3,"dd-mm-yyyy"),'File 2 '!$A$2:$A$16&TEXT('File 2 '!$B$2:$B$16,"DD-MM-YYYY"),0)),"MATCHED","NOT MATCHED"),"NOT FOUND")

There are 3 rows in the example, For the test purpose , 2 should provide a positive result and 1 should give no match.

Thank you

 

[narunfca]

Hi

Enclosed the file. Alter few data to check.

with best regards

Arun

Do you like the formula or any additional requirements?

 

[david gabra]

that is amazing. the fomulae is great.

I just noticed one thing, that that the amount to look for is not always the same in both tabs (when i ran for real)

Can, we add a tolerance of amount to check of above or below 10%?

thanks

 

[david gabra]

also what do you mean
'with helper column' and 'without helper column'
what is the the difference between the two
thanks

 

[narunfca]

Enclosed the file with tolerance.

with best regards
Arun N

 

[Nebu]

Hi:

Does this non array formula do what you are looking for?

=IFERROR(IF(ABS(F2-INDEX('File 2 '!$C$2:$C$16,MATCH(1,MMULT((A2='File 2 '!$A$2:$A$16)*(C2='File 2 '!$B$2:$B$16),1),0)))/F2<0.1,"Matched","Not Found"),"Not Found")

Thanks

 

[narunfca]

Hi:

Does this non array formula do what you are looking for?

=IFERROR(IF(ABS(F2-INDEX('File 2 '!$C$2:$C$16,MATCH(1,MMULT((A2='File 2 '!$A$2:$A$16)*(C2='File 2 '!$B$2:$B$16),1),0)))/F2<0.1,"Matched","Not Found"),"Not Found")

Thanks

A small change, instead of first "Not found", it should be "Not Matched".
The formula will be

=IFERROR(IF(ABS(F2-INDEX('File 2 '!$C$2:$C$16,MATCH(1,MMULT((A2='File 2 '!$A$2:$A$16)*(C2='File 2 '!$B$2:$B$16),1),0)))/F2<0.1,"Matched","Not Matched"),"Not Found")

with best regards
Arun N