FORMULA(S) ERRORS

Bobby1

Blank diagram.pdf

I have been trying to "MATCH/NO MATCH" values in

sheet A with Sheet B. first by columns to ensure data integrity then to do a formula that will return MATCH/NO MATCH if all criteria is met, with an "@cell " formula to match

=IF(COUNTIFS({PAYEE}, FIND(Payee@row, @cell) = 1, {DATE}, FIND([Invoice Date]@row, @cell) = 1, {INV #}, FIND([Invoice No.]@row, @cell) = 1, {AGRMT TYPE}, FIND([Agrmt Type 1]@row, @cell) = 1, {AGRMT # TEST}, FIND([Agrmt No. 1]@row, @cell) = 1, {TOTAL $ AMNT}, FIND([Total Inv. Amount]@row, @cell) = 1), "MATCH", "NO MATCH")

or a similar formula without the "FIND" "@CELL"

But I CANT SEEM TO GET PASSED THE " MATCH","NO MATCH", WHEN I CAN SEE A MATCH OR INVALID DATA TYPE. FYI ALL THE COLUMN PROPERTIES ARE THE SAME.

Genevieve P.

Hi @Bobby1

The COUNTIFS Function works as a sort of filter. This means you don't need the FIND and @cell, you can just list the Column and Criteria one after the other, like so:

COUNTIFS({First Column}, "Criteria", {Second Column}, "Criteria")

or in your case:

COUNTIFS({PAYEE}, Payee@row, {DATE}, [Invoice Date]@row, {INV #}, [Invoice No.]@row, {AGRMT TYPE}, [Agrmt Type 1]@row, {AGRMT # TEST}, [Agrmt No. 1]@row, {TOTAL $ AMNT}, [Total Inv. Amount]@row) 

Then what you can look for is if that COUNT is greater than 1 (so there's at least one row that matches all of those criteria). If it's greater than 1, return "MATCH", otherwise there are 0 rows matching, so "NO MATCH":

=IF(COUNTIFS({PAYEE}, Payee@row, {DATE}, [Invoice Date]@row, {INV #}, [Invoice No.]@row, {AGRMT TYPE}, [Agrmt Type 1]@row, {AGRMT # TEST}, [Agrmt No. 1]@row, {TOTAL $ AMNT}, [Total Inv. Amount]@row) > 0, "MATCH", "NO MATCH")

Let me know if that makes sense!

Cheers,

Genevieve

Bobby1

@Genevieve P. Thank you. it seems (>0) resulted in a more accurate match no match. this also presented something I had not thought about which is blank cells. if there are matching blank cells it should return (Blank) instead of match.

Lastly, when there is a match and all criteria is met, I would like to have a column populate a Unique ID from sheet A to sheet B

are these possible?

Genevieve P.

Hi @Bobby1

Can you clarify what you mean in regards to the blank cells? Do you mean if all cells in the current row are blank? If so, you can write an IF statement to check this before the IF(COUNTIFS formula:

=IF(AND(Payee@row = "",  [Invoice Date]@row = "",  [Invoice No.]@row = "", [Agrmt Type 1]@row = "", [Agrmt No. 1]@row = "", [Total Inv. Amount]@row = ""), "BLANK", ...other formula

So something like:

=IF(AND(Payee@row = "",  [Invoice Date]@row = "",  [Invoice No.]@row = "", [Agrmt Type 1]@row = "", [Agrmt No. 1]@row = "", [Total Inv. Amount]@row = ""), "BLANK", IF(COUNTIFS({PAYEE}, Payee@row, {DATE}, [Invoice Date]@row, {INV #}, [Invoice No.]@row, {AGRMT TYPE}, [Agrmt Type 1]@row, {AGRMT # TEST}, [Agrmt No. 1]@row, {TOTAL $ AMNT}, [Total Inv. Amount]@row) > 0, "MATCH", "NO MATCH"))

Or did you only need to check specific cells? (If so, remove out the ones that you don't need to check from the AND statement).

For your second question, it actually sounds like you're looking for a different formula. An INDEX(COLLECT formula can bring back data instead of just counting or summing across sheets. Here's an article that explains that structure: Formula combinations for cross sheet references

In your case, something like this:

=IF(AND(Payee@row = "", [Invoice Date]@row = "", [Invoice No.]@row = "", [Agrmt Type 1]@row = "", [Agrmt No. 1]@row = "", [Total Inv. Amount]@row = ""), "BLANK", INDEX(COLLECT({Column to Return}, {PAYEE}, Payee@row, {DATE}, [Invoice Date]@row, {INV #}, [Invoice No.]@row, {AGRMT TYPE}, [Agrmt Type 1]@row, {AGRMT # TEST}, [Agrmt No. 1]@row, {TOTAL $ AMNT}, [Total Inv. Amount]@row), 1))

Cheers,

Genevieve

Bobby1

@Genevieve P. THANK YOU. the formula worked like a dream. Now I am wondering if there is a way that I can reference 3 sheets index collect to return row Id? or should I have index collect column for each sheet i am referencing?

ex Sheet a-2020/sheet B 2021/ sheet C 2022.

The idea is to return row Id when all matches has been made

Genevieve P.

Hi @Bobby1

You can add three formulas together using the + symbol.

INDEX(COLLECT(sheet 1)) + INDEX(COLLECT(sheet 2)) + INDEX(COLLECT(sheet 3))

If you want the sheet name with it, you'll need to add that in to your formula.

"Sheet A - " + INDEX(COLLECT(sheet 1)) + "Sheet B - " + INDEX(COLLECT(sheet 2)) + "Sheet C - " + INDEX(COLLECT(sheet 3))

Keep in mind if you don't have a match you'll see an error. It may be best to add an IFERROR around each INDEX(COLLECt to identify when there is no match for that sheet:

"Sheet A - " + IFERROR(INDEX(COLLECT(sheet 1)), "No Match") + "Sheet B - " + IFERROR(INDEX(COLLECT(sheet 2)), "No Match") + "Sheet C - " + IFERROR(INDEX(COLLECT(sheet 3)), "No Match")

Cheers!

Genevieve

Bobby1

Thank you again @Genevieve P.

circling back to individual column match : =IF(COUNTIF({(2020)Invoice 9.9.22 INV #}, [Invoice No.]@row) > 0, "M", "NM") is returning "M"-Match when there isnt one

also when i change the value to i get NM-No Matches when there is one

Genevieve P.

Hi @Bobby1

It looks like it's finding a match because there's a cell with a blank value in your first sheet. You would need a statement in the beginning to check for a blank cell, like so:

=IF([Invoice No.]@row = "", "NM", IF(COUNTIF({(2020)Invoice 9.9.22 INV #}, [Invoice No.]@row) > 0, "M", "NM"))

Cheers,

Genevieve

Bobby1

@Genevieve P. Let me start off by saying you have been a tremendous help and have made a world of a difference in being able to implement process improvements.

AN additional layer to this series is. for the =IF([Invoice No.]@row = "", "NM", IF(COUNTIF({(2020)Invoice 9.9.22 INV #}, [Invoice No.]@row) > 0, "M", "NM"))

how might I be able to return if both sheets cells are blank ex =IF([Invoice Date]@row = "", "FONM", will identify a blank in sheet (B), how can I identify When sheet (A) only has a blank? or when both cell in the different sheets are blank (a) + (b)

Genevieve P.

Hey @Bobby1

I'm glad I've been able to help!

However I'm not quite sure I understand this next layer.

With the current formula, it's checking if the row in this current sheet [Invoice No.]@row is blank. If it is blank, it won't look for a match in your second sheet.

This means that if both cells are blank, it won't find these as "matching rows" because it's not getting to that part of the formula - it stops as soon as it recognizes this first sheet has a blank cell.

Then in the scenario where this current cell, [Invoice No.]@row, is NOT blank, it will check your second sheet for a matching Invoice Number. If there is no matching invoice number (because the second sheet has a blank cell for that column), then it will return "NM", meaning it could not find a match.

Is that what you're wanting to do?

Bobby1

YES