Returning value if there is a difference

[deancorleone]

Hi all

"In cell M7 I would need a formula to say if Cell M4 does not equal 0 then return the film, country and Product name of where the difference is"

I have a issue with a reconciliation file that I thought should be simple, I have tried using some Index Match array's but have been unsucessful so far.

I have attached a "small sample" in the workbook.

I have a bunch of films in column B
a Country, US, & Canada in column A (there would be a lot more) the films repeat
A product code in column C
and the amount sold in column D

Then the same information from another source file in colums G-J

In cell M4 there is a calculation to see if there is a difference between the two totals in row 30

In cell M7 I would need a formula to say if Cell M4 does not equal 0 then return the film, country and Product name of where the difference is.

Is there a way to return multiple differences, right now the difference lies in row 4, but if there was a difference in row 20 and row 29 also?

Any help anyone can provide would be so amazing! I will as always try in the meantime!

[kvsrinivasamurthy]

Pl see the attached file with ARRAY formula.

[deancorleone]

You guys never cease to amaze me! thank you so much! I have really learnt so much from this forum, hopefully with enough practice ill be able to contribute one day.

I shall mark this off as solved

Thanks AMkvsrinivasamurthy

[deancorleone]

hey, one follow up question. Would you be able to help me out with the formula sourcing data from a different tab so the result showed up on a separate tab?

Thanks again so much!

[deancorleone]

One other thing i crudely concatenated it so it all shows up in the same cell

=CONCATENATE(IFERROR(INDIRECT("a"&SMALL(IF($D$2:$D$29<>$J$2:$J$29,ROW($J$2:$J$29),""),ROW(A1))),"")," ",(IFERROR(INDIRECT("B"&SMALL(IF($D$2:$D$29<>$J$2:$J$29,ROW($J$2:$J$29),""),ROW(B1))),"")))

Is there a limit as to how many arguements I can concatenate?

Thanks again and sorry for so many questions, i am a curious learner

[Pepe Le Mokko]

As this is a different question, please start a new thread, or ask a mod to help you move your question

[Pepe Le Mokko]

As this is a different question, please start a new thread, or ask a mod to help you move your question