Find a text string and an amount in a vlookup formula

[nielsss]

Hi there

I'm working on an export of our sales. Workbook contains 2 sheets:

Sheet1
It shows a list of all our booking IDs and the sales activity. For each booking ID in column E "Booking ID", there is always a "Sale created..." text string, and often 1 or several "Sale adjustments" with positive or negative numbers in a text string in Colum I "Comment". Then the value of the Sale created / Sale adjustment is found in column J "Operator amount"

A booking ID often occurs more than once in the list if more sales adjustments are made.

For each booking ID, I would like to return (sum of) the Operator Amount if:
- Booking ID matches.
- Text string contains either the exact string "Sale created" or "Sale adjustment created". For both these options, I have created 2 output columns in sheet2.

Sheet2:
The output could contain these columns:
A: a list of unique booking IDs;
B: column for "Sale created" and the starting value of the sale
C: column for "Sale adjustments": the sum of all positive/negative adjustments)

I figured I would do a vlookup for column B and C; lookup the Booking ID in sheet1, then search for the exact text match "Sale created" in sheet1 column I, and then return the Operator Amount.

Please see attached a sample workbookg.

Thanks in advance.

[bebo021999]

How abouut in B2:

Please Login or Register  to view this content.

Drag down and accross

[nielsss]

Superb, many thanks