Lookup multiple values with 1 lookup reference

[ceconnor]

Hi,

I am really struggling with a way round this, will try to explain as simply as possible:

- I want to find the monthly revenue generated by different contracts for my company. Each contract has a reference number (this is the unique ID/lookup).
- Since monthly revenue reports are not available, I need to look at the revenue YTD for the current month vs. the revenue YTD for the previous month and find the difference between the two, for each contract.
- I have tried using VLOOKUP and SUMIFS but these are limited since the contract reference can appear multiple times with different values (revenues generated across different business lines). Sometimes the values are positive, sometimes negative. VLOOKUP and SUMIFS returns the same value (the first it finds) and repeats, it is not accurate.
- Is there a formula that I use to search by the contract reference in the previous months spreadsheet, and return the revenue produced by each service line? i.e. 1 lookup criteria that Excel will use to return all values corresponding to that lookup criteria?

Hope that makes sense. I am an Excel novice and am teaching myself mainly through these forums - simple language or examples will help my learning!!!!

 

[ceconnor]

As an example, this is the kind of thing I would be searching for in my previous monthly revenue data. The contract ref - 12345 - never changes. The business line can be any of the business lines offered by the company. The revenue changes according to each business line.
I would need a lookup function which allows me to search "12345" and return 1000, 2000, -1000, 1500 on separate lines (i.e. same format). The ones I tried are just returning "1000" as this is the first value it finds.

Contract ref Business line YTD Revenue
12345 Clothes 1000
24689 Shoes 2345
13579 Makeup 2000
12345 Shoes 2000
12345 Makeup -1000
12345 Clothes 1500