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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,119
Office Version
  1. 365
  2. 2010
Please use XL2BB to show some examples and what you want as the results.
 

figaro

New Member
Joined
May 6, 2004
Messages
23
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.
 

figaro

New Member
Joined
May 6, 2004
Messages
23
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
 

figaro

New Member
Joined
May 6, 2004
Messages
23

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,138
Office Version
  1. 365
Platform
  1. Windows
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’)
 

figaro

New Member
Joined
May 6, 2004
Messages
23

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,138
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=FILTER(Sheet2!$D$2:$D$6,(Sheet2!$A$2:$A$6=A2)*(Sheet2!$E$2:$E$6=C2),"")
 

figaro

New Member
Joined
May 6, 2004
Messages
23
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,138
Office Version
  1. 365
Platform
  1. Windows
You just need to change the ranges to suit.
 

Forum statistics

Threads
1,144,695
Messages
5,725,807
Members
422,642
Latest member
praveenlal

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
Top