Forum Discussion
Compare 2 lists of data and identify a cell that meets certain criteria
Below I have 2 lists and you will see a few lines are on both list but there are a few that are on 1 but not the other.
If I am looking on my SCH List and see an invoice on this list that is over $1,000 and is not on my Session list I need to identify it. If you look at the SCH List you will see CT Corporation invoice # 23256391-RI but that invoice isn’t on my Session list. So I need a way to identify it. Does this help explain what I am trying to do ? Again thank you so much for your time and help with this.
Session List:
Vendor ID | Vendor Name | Invoice # | Date | Invoice Amount |
12873 | CSC | 81039848 | 4/9/2022 | $164.40 |
10868 | CT Corporation | 23861767-RI | 4/14/2022 | $313.70 |
27398 | Quill Archivists | 186 | 3/31/2022 | $17.60 |
19850 | Milton A. Policzer | 322 | 3/1/2022 | $935.50 |
SCH List:
Invoice Number | Vendor | Vendor name | Sep. Check? | Pmt. Amount |
23256391-RI | 10868 | CT Corporation | No | $1,467.43 |
80326081 | 12873 | CSC | No | $159.60 |
81039848 | 12873 | CSC | No | $164.40 |
322 | 19850 | Milton A. Policzer | No | $935.50 |
186 | 27398 | Quill Archivists | No | $17.60 |
=IF(AND(D40>1000,ISNA(MATCH(C40,Sessions!$A$3:$A$1219,0))),"above 1000 and not in Sessions","")You can try this formula. It's shorter than the original formula and it's adapted to the layout of your data.
6 Replies
Maybe with conditional formatting. In the attached file the invoice numbers in the SCH list are highlighted if the amount is above 1000 and the invoice number is missing in the SESSION list.
OliverScheurich Thank you for the quick response. I have attempted to the formula but its not working. The below line is on my SCH list and is not on my Session list so the formula should return and "Above 100 but not on Session" but unfortunately its not giving me anything.
Here is the formula i am using:
=IF(D40<=1000,"",IF(AND(D40>1000,NOT(ISNA(MATCH(C40,Sessions!$A$3:$A$1219,0)))),"","above 1000 and not in SESSION list"))
Any additional help would be greatly appreciated.
Can you upload a file which shows where the data is entered in your sheets? For example in the attached .pdf file you can see where the data is entered.
The most convenient way would be if you can enter sample data in the .xlsx Excel file however.
