Two Condition Vlookup?

[Braech]

I am currently trying to create a function that searches through an
array for two exact column values which allows me to retrieve a third
column value. For example:

Type of Fruit Date Packaged Amount of Fruit
Apples June 100
Oranges June 50
Apples July 75
Grapes July 50

In this example, I would be looking to retrieve the Amount of Fruit
(Apples) that was packaged in July. I would be putting this into
another worksheet for export formatted as such:

Apples Oranges Grapes
June Function Modified Function Modified Function
July

Any recommendations or suggestions on how I could accomplish this?
Thanks.

Garth

 

[Braech]

Perhaps these are the functions I should use:

http://groups.google.com/group/micr...p+two+condition&rnum=4&hl=en#9d50f849763849d1

Let me check.

 

[Roger Govier]

Hi

Assuming source is on Sheet1 and report on sheet2, and row 1 of sheet1
is a header row,
enter the following in Sheet2 cell B2 and copy across and down as
required.

=SUMPRODUCT(($A$2:$A$1000=B$1)*
(TEXT($B$2:$B$1000,"mmmm")=$A2)*
($C$2:$C$1000))

Amend ranges to suit.
Also assumed entries on report sheet are text "June" etc. as shown

 

[Bernard Liengme]

There is an easier way than VLOOKUP
Suppose your data starts in A1 of Sheet3. Top row is:
Type of Fruit Date Packaged Amount of Fruit
On another sheet beginning in A1 you have
Apples Oranges Grapes (Apples
is in B1)
June XXXX
(June is an A2)
July

The function (XXXX) needed is
=SUMPRODUCT(--(Sheet3!$A$2:$A$5=B$1),--(Sheet3!$B$2:$B$5=$A2),Sheet3!$C$2:$C$5)
The use of absolute references ($) makes it possible to copy this formula.
The more I look at the question, the more 'homeworkesk' it seems!
For explanation see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes

 

[Bernard Liengme]

Then again, you could use a Pivot Table

 

[Braech]

Thanks Roger. For some reason that darn SUM in the title of the
function kept on making me ignore investigating that function.

Garth

 

[Braech]

Thanks as well Bernard. I had considered making a PivotTable, but the
function style query seems to give me a little more cell flexibility.
My knowledge on where the fields would go in the PT also throws me off
a little.

Garth

 

[Braech]

Sounds homework-esque due to the probably common nature of this query.
Would be a little late in the year for this simple of a question
though. Trying to automate things as much as possible from some
exported data.