 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2006Two Condition Vlookup?
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 Perhaps these are the functions I should use: http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/8bd74 dd65151b355/9d50f849763849d1?lnk=gst&q=vlookup+two+condition&rnum=4&hl=en#9d50f8 49763849d1 Let me check. > 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 [quoted text clipped - 18 lines] > > Garth 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  SignatureRegards Roger Govier >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 [quoted text clipped - 18 lines] > > Garth Thanks Roger. For some reason that darn SUM in the title of the function kept on making me ignore investigating that function. Garth > Hi > [quoted text clipped - 37 lines] > > > > Garth 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 > Thanks Roger. For some reason that darn SUM in the title of the > function kept on making me ignore investigating that function. [quoted text clipped - 42 lines] > > > > > > Garth 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. > Thanks as well Bernard. I had considered making a PivotTable, but the > function style query seems to give me a little more cell flexibility. [quoted text clipped - 49 lines] > > > > > > > > Garth 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 SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email >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 [quoted text clipped - 18 lines] > > Garth Then again, you could use a Pivot Table SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email >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 [quoted text clipped - 18 lines] > > Garth
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.