Compare 2 sales reports.

Hi

In H9 copied down on Sales Report

Formula:

=INDEX('Sales per Catogory'!$A$1:$AO$1000,MATCH(C9,'Sales per Catogory'!B:B,FALSE)+12,40)

Change row 1000 as appropriate

Hi,

That's because your Sales per Category Data is not consistently laid out.

In most cases between the Row containing the Client Code and the row containing the Value there are 12 rows including the two rows. e.g 5 & 17, that's why you see the +12 in the formula. Where you have zeros the data format is different. In the case you cite for instance the rows are 356 & 367, i.e. only 11 rows inclusive.

For tasks like these you do really need to ensure your data is consistent otherwise the results are compromised. Sometimes it's possible to work around these things by building more complicated formulae to recognise this situation and adjust accordingly, but there needs to be some consistent rule that can be used to calculate where the amount cells are.

Are you able to normalise your data first. One other thing. I note that you have merged cells on the Sales per Cat sheet. Avoid merged cells at all costs. They are more trouble than they are worth causing difficulties with various calculations and macro processes and not to be recommended unless absolutely necessary.

Hi,

Good news.

I see on looking further that the code is also in column U, and in this case it's always on the same row as the amount value.

Hence you can use

=INDEX('Sales per Catogory'!$A$1:$AO$1000,MATCH(C9,'Sales per Catogory'!U:U,FALSE),40)

Re your other Q.

Put
=IFERROR(IF(U17<>"",MATCH(U17,'Sales Report'!C:C,FALSE),""),"Not Present")

in BC17 on the Sales Cat sheet and copy down. You'll see "Not Present" where the code doesn't exist on the Sales Report. You could also if you want use a conditional format in column U to detect the value Not Present in col BC and colour the cell as you wish.