#### 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.

2 Replies
569 Views

Similar Articles

[PageSpeed] 44

```=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

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

Similar Artilces:

NESTED IF STATEMENTS #2
Having a little problem in an excel class, hoping someone might be able to help. I'm trying to fill in the discount column on a spreadsheet with the following info. The problem states use a nested form of the IF function to calculate 15% if Amount is greater that \$300., 10% if Amount is greater than \$100., and 0% if Amount is less than \$100. The Amount column is cell D5. Anyone know how to write the formula and explain how to go about it? Any help would be much appreciated. Let's see. This is homework so you should come up with the answer yourself. Did you start by looking in the he...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =\$C\$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

Multiple PST Files #2
I am running Outlook 2003 and would like to have Outlook use two different .pst files (one for my work stuff and one for home) on the same computer. Is it possible to do this? If so, can I can I configure one of the pst files to get e-mail from one account, while the other pst file is configured to get mail from the other account? Thanks! -D Yes, you can create multiple PSTs. I keep mine stored in the same file location but with different names. Depending on which version of Outlook you run, you can creat a new pst from file/new/Outlook Data File (I run Outlook 2003 but had multiple P...

HELP!!! The main menu bar is missing off my excel sheet and I can't seem to find anyone to help me get it back. The menu I am missing is the one that includes - File, Edit, Insert and etc. I have tried...right clicking - customize - toolbars, but I don't seem to have the needed item. Anyone with suggestions - please reply... Thanks. Hi if you right-click on the menu bar you should see an item like 'General'. Check this item -- Regards Frank Kabel Frankfurt, Germany JoEllen OSBDC wrote: > HELP!!! The main menu bar is missing off my excel sheet > and I can't s...

Unicode "private use" glyphs in Powerpoint and Word 2008 #2
Office 12.0.0.1 on Leopard 10.5.2, all updates applied to both. A friend is migrating from Windows to Mac, and has come across something that we're trying to work around. He has some .ppt files with font glyphs in from a Unicode .ttf font (a specific purpose font for showing the LCD display characters on a piece of monitoring equipment). When we bring the font and .ppt over to the Mac, the glyphs from the normal ASCII range come over into Powerpoint 2008 fine, but the glyphs from the "private use" range from F000 upwards a way don't transfer. In Powerpoint they show as spac...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

Send 1 address to 2 mailboxes

Column comparing
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Check your earlier post. Dave T wrote: > > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > this please? > > Many thanks > DT -- Dave Peterson ...

Publisher 2000? #2
Is there a place where I can still get version 2000? I've been looking, but all I can find are updates and the 2007 trial. My computer can't run 2007. *If* if can be had, try something like eBay. (I wouldn't buy software that way but that's just my preference.) You may find you have better luck finding a leftover copy of Pub 2003 from some online retailers. (I actually prefer that over 2000.) -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Jason" <Jason@discussions.microsoft.com> wrote in me...

Compare 2 different workbooks with the result in a 3rd
I have two workbooks (2005 Sales, 2004 Sales), which track daily results in half hour intervals. I want to be able to show the increase in 2005 in a 3rd workbooks. The first two workbooks are identically formatted. How can I do this? Many thanks to all in the forum who have helped in the past. If the data is in exactly the same position in the two worksheets you could copy/paste one's data to a new worksheet and then copy the second's, doing an Edit, Paste Special, Subtract on top of the first's data. This is admittedly crude but it is easy to do. -- Jim Rech Excel MVP &q...

Is there a way to do this, or an add-in? TIA. Rich On Tue, 26 Apr 2005 04:44:35 -1000, rmc <rmc@nospam.com> wrote: > Is there a way to do this, or an add-in? What version of Outlook are you using? -- -Ben- Ben M. Schorr, MVP Hi Ben: 2003 SP1 Rich "Ben M. Schorr - MVP" <bens@bogusaddress.mvp> wrote in message news:op.spuxh8sf6r4cx1@bms-tablet2.scgab.com... > On Tue, 26 Apr 2005 04:44:35 -1000, rmc <rmc@nospam.com> wrote: > > > Is there a way to do this, or an add-in? > > What version of Outlook are you using? > > > -- >...

Hyper link one column to another
I would like to have my columns hyper link one another. For example: Click on B2 would take you to N2, and vice-versa. Click on C2 would take you to O2... and so on through column j linked to V. Is this possible without having to make the link for each cell? This is a timesheet template and the columns b though J are the hours and N through V are the text comments for those hours. When I copy the template to a new sheet, there will be no data in any column. I would like to be able to enter an amount or formula (=end-start) for time spent and then be able to click on that cell and hyperlin...

import from eudora #2
I have used Eudora for many years, but am planning to set up my email in outlook. I want to move all of my mailboxes to outlook. Eudora does not have an export function. Do I need to go through something else, such as Thunderbird. I can't use Outlook Express, because it only imports through Eudora v3.0. I have 4.3. Any thoughts on this? "Nate Rosenthal" <ncrose@comcast.net> wrote in message news:IOmdnV5bH5fEddbVnZ2dnUVZ_rbinZ2d@comcast.com... >I have used Eudora for many years, but am planning to set up my email in >outlook. I want to move all of my m...

combining columns all the way down
I am trying to combine two columns of information in excel but th concatenate function doesnt seem to work for it. It may just be m ineptitude in excel but I just cant seem to figure this out. I have 3 colums Column A__________Column B___________Column C tree_______________ .jpg dog________________.gif House______________.png But I cant seem to be able to make it so that column C has tree.jpg an dog.gif and house.png Is concatenate the wrong thing to use here? or am I making my formula incorrectly -- sparkrom ----------------------------------------------------------------------- sparkro...

Named ranges
Excel 2003 I have a total sheet that gets data from three different shift sheets (all in the same workbook) for a monthly report. I have named ranges on each shift sheet for each month's production data (S1M1, S1M2, S1M3, S2M1...) where S1M1 =A2:AC147, S1M2 = A148:AC329, etc. The old formula was:=SUMIF('SHIFT 1'!\$L\$1:\$L\$147,"BB",'SHIFT 1'!\$T\$1:\$T\$147). I'm stumped as to what the new formula would be - how would I direct Excel to column L of S1M1 to search for "BB", then add the contents of column T? The range changes each month, and I tho...

Look up column name, match rows (a/cnumber) & summing up....its confusing!
Hi All, Here is what I'v been trying to do but..! I'v 2 sets of data (data1 & data2) Column name may be sane/different in each data set and same applies to acct_no.. I want to prepare a report that combine product & accounts data show accumulatd result on another sheet/file. Data 1 - upto Jan 2006 Acct. No Total Prod - A Prod - B Prod-C 1001 51 10 30 11 1002 47 15 20 12 1003 80 20 15 45 1004 64 25 16 23 Total 242 70 81 91 Data 2 - Feb 2006 Acct. No Total Prod - A Prod - B 1002 7 5 2 1004 16 10 6 1009 9 3 6 Total 32 18 14 Report required Acct upto Jan'06 Prod - ...

vb equivalent to lookup
I'm reading a file in which I need to see if a variable in the record I'm reading is part of a known list. I had hoped to use "lookup" but this is only an worksheet function. How can I do this in VB? Hi application.worksheetfunction.lookup -- Regards Frank Kabel Frankfurt, Germany "C0ppertop" <C0ppertop@discussions.microsoft.com> schrieb im Newsbeitrag news:2CADA744-9697-4A1F-B260-FCD29685F4E8@microsoft.com... > I'm reading a file in which I need to see if a variable in the record I'm > reading is part of a known list. > I had hoped to ...

Excel AutoSave #2
In Excel 2000 was this feature available? User is not seeing it. Excel 2000 had the AUTOSAVE.XLA Add-in Should be in Tools>Add-ins. If not available there, do a file search on your computer. If not there, install from the CD. Gord Dibben Excel MVP On Thu, 6 May 2004 13:26:16 -0700, "Ssolano" <anonymous@discussions.microsoft.com> wrote: >In Excel 2000 was this feature available? >User is not seeing it. ...

Need to have more Columns available in advanced view
I know how to add columns in advanced view but i can't add all the columns I'd like to add. I can choose more fields (attributs) as search attributes than as result columns. Is there somewhere a switch to turn a field (attribute) into not only beeing searchable but selectable as a column in advanced search? Example: "Invoice Product": Is there a way to make an advanced search or view which delivers field (attributes) of "Invoice Product" as a result? Marko ...