Compare sheets

figaro

New Member
Joined
May 6, 2004
Messages
23
I have two sheets where I need to compare large amounts of data. I need to find a way to create a formula that will compare data in two columns and return the value in a third. Like if the ID in column a, and the date in column c matches from both sheets will bring back the value in column f.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Please use XL2BB to show some examples and what you want as the results.
 
Upvote 0
I have to match column a from sheet one to sheet two. Then if that matches have the date on sheet one colum b match the date in sheet two column d. If that match I need the price on sheet 2 column g return as the result on sheet one column h.
 
Upvote 0
this is an example. i need to see if sheet one column A matches column A on sheet 2. then if the date in column C on sheet one matches the date in column E on sheet two. then if they match i need to return the amount in column D from sheet 2 returns as a value in column e on sheet one.

EmployeeIDLastNamePayrollDateReconciledsheet 1
101146141​
Mardis
1/8/2021​
107.3​
101146141​
Mardis
1/22/2021​
107.3​
101146141​
Mardis
2/5/2021​
107.3​
101146141​
Mardis
2/19/2021​
117.05​
Person NumberLast NameFirst NameAmountDate Paidsheet 2
101146141MardisSydney$ 117.0501/08/2021
101146141MardisSydney$ 117.0501/22/2021
101146141MardisSydney117.0502/05/2021
101146141MardisSydney$ 117.0502/19/2021
101146141MardisSydney$ 117.0503/05/2021
 
Upvote 0
Please use XL2BB to show some examples and what you want as the results.
this is an example. i need to see if sheet one column A matches column A on sheet 2. then if the date in column C on sheet one matches the date in column E on sheet two. then if they match i need to return the amount in column D from sheet 2 returns as a value in column e on sheet one.

EmployeeIDLastNamePayrollDateReconciledsheet 1
101146141Mardis1/8/2021107.3
101146141Mardis1/22/2021107.3
101146141Mardis2/5/2021107.3
101146141Mardis2/19/2021117.05
Person NumberLast NameFirst NameAmountDate Paidsheet 2
101146141MardisSydney$ 117.0501/08/2021
101146141MardisSydney$ 117.0501/22/2021
101146141MardisSydney117.0502/05/2021
101146141MardisSydney$ 117.0502/19/2021
101146141MardisSydney$ 117.0503/05/2021
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
I have
Microsoft® Excel® for Microsoft 365 MSO 64-bit
 
Upvote 0
Ok, how about
Excel Formula:
=FILTER(Sheet2!$D$2:$D$6,(Sheet2!$A$2:$A$6=A2)*(Sheet2!$E$2:$E$6=C2),"")
 
Upvote 0
Ok, how about
Excel Formula:
=FILTER(Sheet2!$D$2:$D$6,(Sheet2!$A$2:$A$6=A2)*(Sheet2!$E$2:$E$6=C2),"")
i don't know much about excel so i really appreciate the help. that seemed to work well but for some reason only went down to row 6
 
Upvote 0
You just need to change the ranges to suit.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top