Excel formula - need to compare values from one sheet to another sheet

**dfuhrman8** · 05-03-2022, 11:36 AM

I need a formula that will take a list of values from one sheet, and compare them to a different sheet. Sheet 1 has column A with amounts and then column B with type of charge. Sheet 2 has the type of charges going across in a row - travel, training, meals, etc, and then amounts going down in each column. I need a formula to verify that sheet 1 has all of the information from sheet 2. I would like to do this comparison in a different column and get a True or False answer back.

Attached is a sample worksheet, with the two sheets.

Any help would be much appreciated! Thank you!

**TMS** · 05-03-2022, 11:40 AM

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

**dfuhrman8** · 05-03-2022, 11:53 AM

I have attached a sample. The only thing missing from my example is for Sheet 1, I do not have the second column showing what type of charges the amounts for column A.

**dfuhrman8** · 05-03-2022, 11:58 AM

Attached is the revised worksheet.

**JeteMc** · 05-05-2022, 01:47 PM

Perhaps something like the following will help.
1. Convert the data range on Sheet2 into an Excel table
2. Use the following Power Query Advanced Editor code to put the table into column form as modeled in columns E:F on Sheet1:

Please Login or Register  to view this content.

3. Use the following to compare the values in Sheet1 column A to the values in the newly produced table: =COUNTIFS(A$1:A$118,A1,B$1:B$118,B1)=COUNTIFS(Table1_2[Value],A1,Table1_2[Attribute],B1)
Let us know if you have any questions.