Lookup issues due to multiple same value data

[Lostinxcel]

Hi,

I am trying to find a solution for the following.

I have a worksheet in which there appears same value data multiple times, i.e. if you look in the work sample, OTD11/01 can be found in 4 rows in column I of sheet "invoice cost overview". In the sheet "freight expenses by ship" this same data will only be found once. This data is the only common value between the two worksheets.

I want to duplicate the "value" column D (sheet "invoice cost overview") and the date found in column B (sheet "invoice cost overview") in " recharge"column G and "date sales inv" column H, both in sheet" "freight expenses by ship", based upon a search of "shipping no." column A (sheet "freight expenses by ship").

However because of the duplicate shipping no's, the known lookup solutions known to me are not working. Is there a way that finds all the requested data and will combine the "value" of all the same "shipping no." found into the one cell in "recharge" column G. The "date" will always be the same date for all the same "shipping no.'s", so that will be a single transfer from one cell to another.

I tried to explain it as good as possible, but if I have failed in doing so, please let me know and I'll give it another try ;-).

Thanks in advance

Erik

[Colin Legg]

Hi,

Yes, if I understand correctly, you can use the SUMIF() function to do this.

The formula in G3 would be:
=SUMIF('Invoice cost overview'!$I$2:$I$14,$A3,'Invoice cost overview'!$D$2:$D$14)

which gives a result of 5,005.01.

Is that the result you're looking for?

[NBVC]

If the date is always the same for each Shipping No. as you infer, then use Sumif

e.g. =SUMIF('Invoice cost overview'!$I$2:$I$14,$A3,'Invoice cost overview'!$D$2:$D$14)

to get the date:

=INDEX('Invoice cost overview'!$B$2:$B$14,MATCH($A3,'Invoice cost overview'!$I$2:$I$14,0))

both copied down

[Lostinxcel]

Thank you very much Colin and NBVC, works great.

Cheers,

Erik