lookup - formula returns wrong value/sum

Thanks for the advice.

Well, I have a sample where I tested the formula, and it successfully worked. It only does not work for my actual workbook where I have 2000 entries (and I cannot upload that one due to sensitive data). However, I changed the example from 10 lines to 100 lines and still have the correct result. I just dont know what it could be. There is absolutely no difference to the actual mainbook. I even checked for hidden lines or somehting similar, but no result.

Thanks,
A2k

Well, I exactly did that and after 60 seconds I came to the result that there is no difference in the total sum

but please, if it helps, enclosed my sanitized workbook which is causing me troubles.

Thanks for the help,
A2k

I do know that colum I is not unique, since the F&B value is many times the same.
However, I do not need to define lookup upon the F&B value, I need to lookup the confirmation (folio) in column E which is unique, and then the associated F&B consumption of that confirmation.
so basically, find the same confirmation number, then insert the missing F&B consumtption of that number from sheet 2.

Thanks,
A2k

I think you may well find that there some errors in your data.

On a quick look I found a few entries in column E that were duplicated therefore giving the wrong results. Also there were a couple of entries missing from column E therefore returning an error on the lookup.

If you sort your Sheet1 by column E Lowest to highest here are the errors.

Rows 45 & 46 Duplicate values in E
Rows 350 & 351 Duplicate values in E
Rows 681 & 682 Duplicate values in E
Rows 873 & 874 Duplicate values in E

Row 595 on Sheet2 you have a value in I that has two decimal points
Rows 1142 & 1143 No entries in E

Hope that helps?

Well, thanks for looking.

So, basically I have this difference in sums because the data is not unique. So basically if I remove all duplicates this should work out, right?

hmm, guess the advanced filter could be of help.

Thanks for the hint,
A2k