Replacing VLOOKUP with index match with SUM option

[Henceman]

I have attached an example file, with working vlookup formula which Im trying to get rid of, so I do not need time to create pivotable to help the formula out.

What the working lookup is currently doing:

1) 1st worksheet contains list of settlements with amounts, there might be several settlements with same number, but in different value in SEK.

2) Second worksheet contains similar information


1st pivot in worksheet counts the instances of settlements
2nd pivot shows the total settlement values per settlement

vlookup compares the information from 2nd pivot and pivot in another worksheet(pivot3), subtracts the value in 2nd pivot and returns a number.

If the resulting number is zero, that means the total difference between same order in 1st and 2nd worksheet are an exact match and can be handled, N/A means the same settlement is not found and any other number means the sums do not add up.

Right now matching settlements are marked with yellow color.

SO can all this be replaced with handy index match with sumif?

Example:
https://files.fm/u/w7s87c24#_

 

[istiasztalos]

Hi Henceman,

I can view a snapshot of your file with the pivot tables but can't download it, my ISP is blocking the download site...

From what you write I don't think you need any lookup, why don't you use SUMIF on the two original data tables?

 

[Henceman]

Really strange, but heres another link... and another http://www.filedropper.com/grayham
https://ufile.io/44vj5

I dont get how sumif would help, I don`t have the value of settlements known, to search for it.

Hi Henceman,

I can view a snapshot of your file with the pivot tables but can't download it, my ISP is blocking the download site...

From what you write I don't think you need any lookup, why don't you use SUMIF on the two original data tables?

 

[istiasztalos]

Hi !

1st and 2nd pivot can be just one table, you can drag the Amount in SEK column to values field twice, set one to COUNT and the other one to SUM

Putting the SUMIF next to it should produce the same outcome as your Pivot3 and VLOOKUP combination, it works in my test

[TABLE="class: grid, width: 538"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1st Pivot[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Count of Amount in SEK[/TD]
[TD]Sum of Amount in SEK[/TD]
[TD]sumcheck[/TD]
[/TR]
[TR]
[TD]1442017073[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]247.95[/TD]
[TD="align: right"]=H3+SUMIF(grind!B:B,F3,grind!C:C)[/TD]
[/TR]
[TR]
[TD]4580246614[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]714.85[/TD]
[TD="align: right"]714.85[/TD]
[/TR]
[TR]
[TD]4580256033[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2900[/TD]
[TD="align: right"]2900[/TD]
[/TR]
[TR]
[TD]4580259792[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8700[/TD]
[TD="align: right"]5800[/TD]
[/TR]
[TR]
[TD]4580261767[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]384[/TD]
[TD="align: right"]384[/TD]
[/TR]
[TR]
[TD]4580261984[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9726.51[/TD]
[TD="align: right"]-37371.19[/TD]
[/TR]
[TR]
[TD]4580262677[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]93427.54[/TD]
[TD="align: right"]93427.54[/TD]
[/TR]
[TR]
[TD]4580264374[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4580264807[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]867.9[/TD]
[TD="align: right"]149.4[/TD]
[/TR]
[TR]
[TD]4580264840[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]218.08[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Does this help?

 

[istiasztalos]

Alternatively, since the amonts in 'grind' are all negative, you could paste all that data in the same table on 'abc', do the pivot and make SUM of AMOUNT, you'll get 0 where the amounts match and the difference where it doesn't

 

[Henceman]

Thank you for working solution, sorry for late feedback.


Is there also a solution, which compares individual settlements in "abc" and "grind" worksheets and I can filter out the ones with value zero (means same settlement number is found on both worksheets and their difference in amount is zero).
Then I could see the matching "Secular "for the settlement as well.

Attaching vlookup example.

https://imgur.com/a/LdvTM

Note, that this as imagined required pivottable on grind sheet, to take together single instances of settlements with matching Seculars.

Thank you.

 

[Henceman]

Forgot to mention, that if you see different amount than zero and still same Secular, it may have got the value other way, right now im interested only in straight matches with corresponding secular.