Lookup or equivalent based on 2 criteria 2

[krispi]

Hi all - today's problem is as follows:

I'm looking to return a value in Excel, based on data in a second workbook, subject to two conditions being met. Here's an example of the source data:
Criteria1 Criteria2 Value1 Value2
Apples A 10 100
Apples B 20 500
Apples C 5 10
Pears B 100 10
Pears C 50 20
Oranges A 3 10

Desired result:
Apples Pears Oranges
Value1 Value2 Value1 Value2 Value1 Value2
A 10 100 0 0 3 10
B 20 500 100 10 0 0
C 5 10 50 20 0 0
This is not the real data but hopefully gives an idea of what I'm trying to achieve. I'm just trying to get this clear in my head before putting it into code. The ultimate purpose is to autopopulate a predefined template from a number of Excel files generated by SAS.

So basically what I'm tring to achieve is a lookup (or similar), for example, look in the source data for the combination of criteria 1 and criteria 2, then return whichever value I want.

I can 'cheat' and create a new column based on concatenating the 2 criteria, but I don't want to touch the source data, only refer to it.

Hope this is clear, but if not just shout!

any ideas? Cheers, Chris

 

[Salut39]

Hi Chris,

if you want your data to be summarised you can try SUMPRODUCT function.

Yuri

 

[cheerio]

A good explanation of use of SUMPRODUCT in this way can be found at

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[krispi]

Thanks folks - I'd totally overlooked that use of Sumproduct. It's exactly what I need.

 

[GlennUK]

Am just curious, which version of Excel do you have?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[krispi]

I'm running Excel 2003 - why do you ask?

 

[SkipVought]

Hi,

This is a job for PivotTable. It can give you exactly what you seem to want in just a few seconds!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[krispi]

Skip - yes, you're right based on the info I supplied. In fact, in my current process I do it that way. However, my ultimate objective is to create the end product in VBA, so pivot tables wouldn't do it for me. The issues I have are:

I have many source data files feeding into my results template - these can be referenced but not changed.

The end product is a monthy report, and the source data files can change shape each month - in the example given, I might not have any apples one month, but might have a new category, say pineapples, which I need to cater for without manually defining it. The number of rows will also change month by month, but my final report will always be in the same format, that is, I need missing values to be populated by zeroes or dashes.

Does that make sense?

 

[GlennUK]

Oh Excel 2003. It was just that there's a new function in Excel 2007 named SUMIFS for multiple criteria.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AnotherHiggins]

I admit it's still a bit early, but I don't see any reason that Pivot Tables wouldn't still work for you.

- Pivot Tables can be created/edited via VBA
- Pivot Tables can be based on multiple sources
- Pivot Tables can be based on Dynamic Named Ranges
[tab](Deals with variable number of rows in source data)
- Pivot Tables can display fields that are currently empty

For that last one, Right Click the field name Criteria1 and select Field Settings, then check the box beside Show items with no data.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[krispi]

Thanks John and I'm sure you're right - but unfortunately I'm on a deadline and, never having created anything other than a basic pivot table before, I just haven't got the thime to explore your suggestion properly.

I will however take the time to do so as soon as I can.

I'm now getting the results I want using the SumProduct calculations, next step is to productionalise it in VBA (having a few problems with that, so see you in the VBA forum!)