Lookup based on criteria in 2 columns

Hi,
I am trying to use a vlookup or other function to return the value in the 
amount column based on the location and date.  Here is a sample of my data:

Location    Date    Amount
101          9/15/8     10
101          9/16/8     20
101          9/17/8     15
102          9/15/8     50
102          9/16/8     75
102          9/17/8     67

For example if I wanted to return the amount for location 102 on 9/15/8, 
what formula would I use?  I tried using variations of vlookups but had no 
luck.

Thanks,

0
Mike1154 (1216)
9/29/2008 8:31:00 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
601 Views

Similar Articles

[PageSpeed] 31

=SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50)

-- 


Regards,


Peo Sjoblom

"Mike" <Mike@discussions.microsoft.com> wrote in message 
news:8F714178-FE2A-4808-92C1-54B8737B4263@microsoft.com...
> Hi,
> I am trying to use a vlookup or other function to return the value in the
> amount column based on the location and date.  Here is a sample of my 
> data:
>
> Location    Date    Amount
> 101          9/15/8     10
> 101          9/16/8     20
> 101          9/17/8     15
> 102          9/15/8     50
> 102          9/16/8     75
> 102          9/17/8     67
>
> For example if I wanted to return the amount for location 102 on 9/15/8,
> what formula would I use?  I tried using variations of vlookups but had no
> luck.
>
> Thanks,
> 


0
peo.sjoblom (169)
9/29/2008 8:51:08 PM
Hi,

In the following examples my data only goes down to row 7 but you just need 
to adjust the formula for your range.

You can array enter the following formula:

=INDEX(C2:C7,MATCH(E1&F1,A2:A7&B2:B7,0),)

If you enter the Location in E1 and the date in F1.  To array enter it you 
press Shift+Ctrl+Enter rather than enter.

In 2007 you could use 

=SUMIFS(C2:C7,A2:A7,E1,B2:B7,F1)

WARNING:  If the location is entered as text and not a number in column A 
then these two formulas will work but 

=SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50)

will return 0.  To get it to work in that case change the formula to read

=SUMPRODUCT(--(A2:A50="102"),--(B2:B50=DATE(2008,9,15)),C2:C50)

-- 
Thanks,
Shane Devenshire


"Mike" wrote:

> Hi,
> I am trying to use a vlookup or other function to return the value in the 
> amount column based on the location and date.  Here is a sample of my data:
> 
> Location    Date    Amount
> 101          9/15/8     10
> 101          9/16/8     20
> 101          9/17/8     15
> 102          9/15/8     50
> 102          9/16/8     75
> 102          9/17/8     67
> 
> For example if I wanted to return the amount for location 102 on 9/15/8, 
> what formula would I use?  I tried using variations of vlookups but had no 
> luck.
> 
> Thanks,
> 
0
9/29/2008 9:48:02 PM
Reply:

Similar Artilces:

Exchange 2003 database offline defragmentation #2
My Exchange 2003 database is already over 16GB, and must do the offline defragment as soon as possible. Just search the info from Microsoft, but don't have much detail on this. So please help. Thanks. 1. Do I need to stop the 'MS Exchange Information Store', 'MS Exchange Management', 'MS Exchange MTA Stacks', and 'MS Exchange System Attendant' services before I dismount the mailbox store? 2. Is this command correct to run the defragmentation utility on the mailbox store database with a backup? The command is: eseutil /d c:\progra~1\exchsrvr\mdbdata\priv1....

Cant add a column
I have a spreadsheet that i am trying to add a column to and i get the following message; To prevent possible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet. Try to locate the last nonblank cell by pressing ctrl+end, and delete or clear all in cells between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used. Or, you can move the data to a new location and try again. I have tried both and still get the same message. Note im using office 2003. Possibly this................ http://support.m...

How do I put a heading even with a column?
Hello I am looking to add company names as a header but even with columns. Any idea how I might be able to do this Maybe File>Page Setup>Sheet>Rows to repeat at top? Place a company name in each column across row1 Gord Dibben MS Excel MVP On Fri, 14 Dec 2007 07:26:01 -0800, Stressing out <Stressing out@discussions.microsoft.com> wrote: >Hello > I am looking to add company names as a header but even with columns. >Any idea how I might be able to do this I will try that Thank you! "Gord Dibben" wrote: > Maybe File>Page Setup>Sheet>Rows to...

RELAY #2
Hi , How can I disable the relay message in my exchange 2003??? The message? On Sun, 17 Oct 2004 11:21:15 +0200, "Essam" <essam@bk.co.il> wrote: >Hi , > >How can I disable the relay message in my exchange 2003??? > "Essam" <essam@bk.co.il> wrote: >How can I disable the relay message in my exchange 2003??? Sure. Uncheck the box that says authenticated uses can relay. The system's installed with relaying disabled for all but authenticated users so, unless you've changed something, removing that permission will prevent relays. -- ...

vlookup: can i specify the column with a name rather than a number
eg. if a setup an array and name it "array" and the column header in column 6 is "column 6 header", then rather than specify: vlookup(A1,array,6,FALSE) can i put the column header in rather than the number 6 (noting that) vlookup(A1,array,column 6 header,FALSE) doesnt work ...

Returning a lookup value from multiple sources
I have 4 worksheets - WIP, Orders, Invoicing, Costs. In the WIP sheet, I summarise data from Invoicing & Costs by Orde number. Some of the Orders will be invoiced, some have costs, som both. Some order numbers will appear more than once in each sheet, som not in either. Summarising the data is easy, once I have each order using SUMIF, bu how do I get the unique order numbers from the other sheets into th WIP sheet in the first place?? This is driving me mad!! -- Message posted from http://www.ExcelForum.com Hi I assume that your 'Orders' sheet should store all order numbers....

Unknown error 0x80040201 #2
Having trouble with some users, they keep getting the below mentioned error while sending and receiving e-mails. All POP3 settings are correct and mail does get received but while sending the message comes up. Funny thing is that e-mails still go out. Only happens while sending... Error msg: Unknown error 0x80040201 Al <Al@discussions.microsoft.com> wrote: > Having trouble with some users, they keep getting the below mentioned > error while sending and receiving e-mails. All POP3 settings are > correct and mail does get received but while sending the message > com...

Money Repair level 2 crashes
Money 2006 Deluxe was hanging up. I performed a quick repair = fine, but didn't solve the problem. Level2 repair and it crashes and sends details to MS. I uninstalled and reinstalled Money. No change. It crashed during sending payments 2x. By crash, I mean that it hung up. clicking on anything just beeps. Waiting an hour doesn't help. Had to kill the program. The payments 'seem' to have gone though. Any ideas? In microsoft.public.money, Bryan wrote: >Money 2006 Deluxe was hanging up. I performed a quick repair = fine, but >didn't solve the problem. Level2 re...

Child CDialog-based custom control message problem
I have a CFormView based MDI application. I created a custom control, which has a couple static texts, an edit box, a scrollbar and is CDialog-based. This custom control is dynamically placed in the CFormView several times using Create. This all works fine. I'd like to add to this by getting WM_SETFOCUS and WM_KEYDOWN messages for the child dialog so that I can draw a rectangle around the child dialog that has focus and adjust the value of the number the scrollbar and edit box represent using the arrow keys. I'm not getting these messages because I've overrode PreTranslateMess...

Query not dislaying correct column of a field
I have a table that has 2 columns, Abbr. and Long Form ? ? ? ? -- KARL DEWEY Build a little - Test a little "thorpk@gmail.com" wrote: > I have a table that has 2 columns, Abbr. and Long Form > On Fri, 21 Dec 2007 07:24:53 -0800 (PST), "thorpk@gmail.com" <thorpk@gmail.com> wrote: >I have a table that has 2 columns, Abbr. and Long Form That seems reasonable. Are you having a problem? Remember that you can see your computer... we cannot. John W. Vinson [MVP] ...

Outlook 6 #2
I am having problems receiving my email through Outlook Express 6. Everytime I try to send and receive, it doesn't work and an error message pops up that says and Outlook Express store file may be damaged. Please use a utility such as ScanDisk to repair any damaged files.Does anyone know what my problem is? Thanks for your help. Matt This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.publ...

Credit Card Refunds #2
When we have a customer return an item that they have purchased from us on a credit card, we issue a credit memo and then refund to their credit card. The logical way to handle this is with a "negative" cash receipt, applied to a credit memo, just as a cash receipt would be applied to an invoice. There isn't currently a good, logical way of handling this since cash receipts in Sales cannot be a negative number. Instead we have to enter the credit card refund as a withdrawal in Bank Transactions and then enter an invoice to the customer to offset the credit memo. This i...

Office Mac #2
Does anyone think microsoft will put a database application like Access in office mac? In article <E6E68FF4-3832-4404-9914-0AD0B345A39B@microsoft.com>, Glenn <Glenn@discussions.microsoft.com> wrote: > Does anyone think microsoft will put a database application like Access in > office mac? It's a shame when cousins marry. Hi Glen: No. Where is the business case for it? 1) There are any number of perfectly good "database" applications for the Mac. We don't need another. Excel is available, and makes a very good flat-file database. MySQL works real...

Unspecified Automation Error #2
_Yoyo Wrote: > Hi, > > I'm currently using Office 2000 Pro and i wrote some macros for > converting data taken from a ms access table into a .txt file and > others. They worked just fine but i decided to upgrade my XP Os with > SP2 and all of the sudden none of my coded macros work! I believe is > the SP2 because they still work on other computers. Help! Where could I go to get an answer to this problem besides here? -- _Yoyo ...

Reinstalling MSCRM 1.2 failure
Hello everybody, I wanted to reinstall MSCRM 1.2 on our SBS 2003 server but I receive the following error message : "Failed to connect to database _MSCRM on <sql_server_name>. hr: 0x80004005" Can anyone help me !!!! ...

Deleting Sheets Based on Name
Ive got a macro which makes a large number of sheets with information in them.... Ive then got another macro which gets all the information from those sheets.... the problem is... im then left with all the sheets.. and I really want to delete them..... Is there anyway to make a macro which would let me delete sheets.. based on their name..... all the ones i want to delete are called "cats (104)" or "cats (105)" etc etc..... there is one called "cats" which i want to keep.... so basically If it could someonie delete based on the term "cats (&quo...

How do I send an Automatic Message at a pre-defined time? #2
Hi, I 'd like to send an automatic message after a certain action is completed. How do I configure outlook to do this? <fiorentico@gmail.com> wrote in message news:1143146339.932934.203520@i39g2000cwa.googlegroups.com... > Hi, > > I 'd like to send an automatic message after a certain action is > completed. How do I configure outlook to do this? What action? If it is a Outlook event, you will probably have to look at scripting it yourself. -- John Blessing http://www.LbeHelpdesk.com - Help Desk software priced to suit all businesses http://www.room-bookin...

how to resetC:\users\Ron|Appdata\local\microsoft\Outlook\Outlook. #2
This message always apears after I try to start Outlook 2007 . Cannot start Microsoft Office Outlook. Cannot open the outlook window. The set of folders cannot be opened. The file C:\users\Ron|Appdata\local\microsoft\Outlook\Outlook.pst is not a personal folders file. It worked now the bugger has gone on holiday. Please help I can' access any of my stored e-mails You'd get a better answer asking on an Outlook newsgroup http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.outlook.general&cat=en_US_db03bd1f-73aa-41e1-abfd-27a6e3c352e5&lan...

Fixing column width
I have a spreadsheet in which I have set the widths of certain columns using Autofit. The problem is that I often have to add to the spreadsheet by importing data from a text file. Every time I do that, the column width changes to the width of the new data (which is usually narrower than what I wanted). Is there a way to prevent that from happening? Thanks, Cliff Lewis Assuming you're using the Data, Import Data command, when you get to the final "Import Data" dialog there is a "Properties" button (in Excel 2003 at least). One of those properties is Adjust column w...

Hyperlink to first blank cell in column?
(XL2007) Thanks to help from MVP Biff, I can return the row number of the first blank cell in a single-column named range using an array formula (http://tinyurl.com/qb689k). This is a dynamic range that will adjust as new items are added. I'm setting up a workbook in which I have a "Blank Master" sheet and a "Jobs List" sheet. The "Master" sheet will be copied and renamed for each month. I'd like to have a hyperlink on the "Master" sheet that will carry over to each new copied sheet that would take the user to the first blank cell in the JOB...

adding lookup window and retrieve value
hi please I really need help I want to add text box in screen Sales Order Setup "from tools-->setup-->sales-->sales order processing-->button Order" and button to open lookup window of user classes so the problem that I want to open this window and retrieve the returned value into that text box this is the first point and the other point is : after I save that text box with the document id in new table ,and try to retrieve that any document id by pressing lookup button of document I can't retrieve the class again another point: I make the first part by dexterit...

2 clicks won't open Word 2007
I installed Office 2007 recently and am starting to get used to it. Sometimes, and this has been occurring more and more recently, I double-click a word document, and word starts to open, but the file doesn't fully open; I see some sort of word-canvas but can't type on it. If I then click on the file and drag it onto the canvas that seemed to open before, I see a small plus, release the mouse button, and the file opens. Has anyone experienced this problem before? This is NOT normal behavior. What do I have to do to fix this? Thanks! Ryan-- -- Ryan--- If t...

autonumbering a column in excel
How would I go about autonumbering a column in excel? Hi the following formulas will put a sequencing number in column A, if column B is filled: in A1 enter the following formula =IF(B1<>"",1,"") in A2 enter the formula =IF(B2<>"",MAX($A$1:OFFSET($A2,-1,0))+1,"") and copy this formula down for as many rows as you like -- Regards Frank Kabel Frankfurt, Germany Chris Lai wrote: > How would I go about autonumbering a column in excel? ...

Set number format based on cell contents
I have a cell B3 which shows a Data Validation list of £ or % Having selected £ or % in B3 the user then enters a number in C3. I want to have cell C3 formatted to General if B3 = % and formatted to Currency if B3 = £. I'd really appreciate some help Thanks You need VBA for that. Paste the following macro into the sheet module for the sheet you are working on. To do that, right-click on the sheet tab, select View Code, and paste this macro into that module. Note that this macro does exactly what you asked. That is, it will change the format of C3 if you select $...

Enable Command Button base on UserName
I have a Command Button on a worksheet that I only want to display if a specified User Opens the workbook. I have the following code: (in a UserName module) ================= ' By Chris Rae, 14/6/99, 3/9/00. Option Explicit ' This is used by GetUserName() to find the current user's ' name from the API Declare Function Get_User_Name Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, _ nSize As Long) As Long Function GetUserName() As String Dim lpBuff As String * 25 Dim txtName As String Get_U...