Need To Lookup Value Based on Two Criteria

Hi,

I need a formula to return a value from a row in a different worksheet
within the same workbook.  The row must meet a combination of two different
criteria.  I previously used vlookup based on the REF column (see example
below) to return the value in the Total column for that row, but now I am
finding that the values in the REF column are starting to repeat.  Here is
an example:

Sheet1

Date          Name    Ref    Total
08/15/03   XYZ      325      100
08/15/03   def        276      220
02/12/04   abc       396      250
07/02/04   def        325      275

Date column is formatted as date; Name & Ref columns formatted as text;
Total column formatted as number.  Data in any column may be duplicated on
multiple rows, but combination of Date and Ref will always be unique.

Sheet2

Cell A1=325           (formatted as text)
Cell B1=07/02/04  (formatted as date)

Need Formula in Sheet2, Cell A2 to return the value in the total column for
the one row which has the data in cells A1 & B1 (in this case 275).  Again,
the combination of A1 and B1 will always be unique.

I am using Excel 2000 if that makes any difference.

Thanks for all help.


Monte


0
msliger1 (19)
7/20/2004 9:00:16 PM
excel 39879 articles. 2 followers. Follow

2 Replies
484 Views

Similar Articles

[PageSpeed] 37

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet2'!$D$1:$D$100,MATCH(1,('sheet1'!$A$1:$A$100=B1)*('sheet1'
!$C$1:$C$100=A1),0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Monte Sliger" <msliger1@cox.net> schrieb im Newsbeitrag
news:OD4ZKxpbEHA.2352@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I need a formula to return a value from a row in a different
worksheet
> within the same workbook.  The row must meet a combination of two
different
> criteria.  I previously used vlookup based on the REF column (see
example
> below) to return the value in the Total column for that row, but now
I am
> finding that the values in the REF column are starting to repeat.
Here is
> an example:
>
> Sheet1
>
> Date          Name    Ref    Total
> 08/15/03   XYZ      325      100
> 08/15/03   def        276      220
> 02/12/04   abc       396      250
> 07/02/04   def        325      275
>
> Date column is formatted as date; Name & Ref columns formatted as
text;
> Total column formatted as number.  Data in any column may be
duplicated on
> multiple rows, but combination of Date and Ref will always be unique.
>
> Sheet2
>
> Cell A1=325           (formatted as text)
> Cell B1=07/02/04  (formatted as date)
>
> Need Formula in Sheet2, Cell A2 to return the value in the total
column for
> the one row which has the data in cells A1 & B1 (in this case 275).
Again,
> the combination of A1 and B1 will always be unique.
>
> I am using Excel 2000 if that makes any difference.
>
> Thanks for all help.
>
>
> Monte
>
>

0
frank.kabel (11126)
7/20/2004 9:06:50 PM
Hi,

=INDEX(Sheet1!$D$2:$D$5,MATCH(1,(Sheet1!$A$2:$A$5=Sheet2!B1)*(Sheet1!$C$2:$C$5=Sheet2!A1),0))

entered using CTRL+SHIFT+ENTER

adjust the range according to your data

Hope this helps!

Monte Sliger wrote:
> *Hi,
> 
> I need a formula to return a value from a row in a differen
> worksheet
> within the same workbook.  The row must meet a combination of tw
> different
> criteria.  I previously used vlookup based on the REF column (se
> example
> below) to return the value in the Total column for that row, but no
> I am
> finding that the values in the REF column are starting to repeat.
> Here is
> an example:
> 
> Sheet1
> 
> Date          Name    Ref    Total
> 08/15/03   XYZ      325      100
> 08/15/03   def        276      220
> 02/12/04   abc       396      250
> 07/02/04   def        325      275
> 
> Date column is formatted as date; Name & Ref columns formatted a
> text;
> Total column formatted as number.  Data in any column may b
> duplicated on
> multiple rows, but combination of Date and Ref will always b
> unique.
> 
> Sheet2
> 
> Cell A1=325           (formatted as text)
> Cell B1=07/02/04  (formatted as date)
> 
> Need Formula in Sheet2, Cell A2 to return the value in the tota
> column for
> the one row which has the data in cells A1 & B1 (in this case 275).
> Again,
> the combination of A1 and B1 will always be unique.
> 
> I am using Excel 2000 if that makes any difference.
> 
> Thanks for all help.
> 
> 
> Monte 

--
Message posted from http://www.ExcelForum.com

0
7/20/2004 9:12:14 PM
Reply:

Similar Artilces:

Expression for Percentage Calculations Based Upon Subsets of Field
How do you create an expression that will calculate a percentage based upon subsets of two fields? For example, one field is "Status" (there are three options), the other is "Resolved" (populated with a "Yes" in the appropriate cells that correspond to those "Status" entries that are resolved. I must calculate the percentage of "Resolved" entries for each status classification based upon the total number of each status classification. I have created parameter queries that appropriately identify the number of matching records for each S...

Data Validation Help Needed urgently !!!!!!!!!!!
I am trying to write a data validation in VBA. I know I have the code right but I can't get it to work. It works fine if the validation was done dirctly in excel but I get an error when I try the same thing in VBA. Below is a snippet of the code I am having a problem with. The problem seem to be with the " Formula1:=" line. I have been scratching my head for the past 24hrs now trying to figure this out and I am still not coming up with anything. Any help will be greatly appreciated. Range("B3:C3").Select With Selection.Validation .Delete .Add Type...

newbie needs help in Ezcel programming #2
Hi everyone, please help me with this, newbie to Excel. I know what I want but, but dont know how to get it! I have a spreadsheet called Rawdata.xls and within that a sheet called "table a". The sheet contains data within B10:I100 where column b contains a list of non-unique codes, and column C contains a number of unique identifiers. Another spreadsheet called Tables.xls contains a sheet called "Numbers". Cell B10 contains a list of all the codes contained in a defined range (which is the same as all the codes in column b of "table a" in RawData.xls What I ...

I need a running total of monthly grocery expense
I need to keep a weekly total of my grocery expense vs. budget and totaled each month. ...

documents issued in one version need to be updated in another
Can we open a document issued in version 2003 if we have 2007 installed? The document would be emailed to me by another office and I need to be able to open it and work in it. YES John G. "Becky Freedman" <Becky Freedman@discussions.microsoft.com> wrote in message news:A839A4C9-41EA-4FBF-94AF-6974E061D771@microsoft.com... > Can we open a document issued in version 2003 if we have 2007 installed? > The > document would be emailed to me by another office and I need to be able to > open it and work in it. ...

lookup?
I am trying to do a type of lookup in excel. If I input a part number in cell "e12" for example and I have a large parts list in a column "K" and corresponding prices in "L" column, I would like to us a formula to locate the part number from the "K" column and return the corresponding price from column "L" to cell "f12". Put this formula in F12: =VLOOKUP(E12,K:L,2,FALSE) ...

Chart two variables with differing values
I'd like to chart data similar to: Year 2000 2001 2002 2003 2004 Thefts 75 120 95 101 80 Veh. Mfg. 100,200 125000 130000 145000 125000 I want to produce a chart with the year on the abcissa and with the Thefts scale on left ordinate and Veh. Mfg on the right ordinate and have the scales set accordingly. I'm running Excel 2002. Seems i can't select the Format/Selected Series data Series/Secondary Axis to set the scales correctly. I'd like to get som...

Excel Formula for determining azimuth relative to true north between two sets of geographic coordinates?
Using the geographic coordinates for each, I've "translated" the FCC procedure for determining the distance between two transmitters ( per FCC 73.208) into Excel formulas. Excel gives identical distance results to the "fill-in" form provided in www.fcc.gov/mb/audio/bickel/distance.html . But there is no comparable formula in the FCC R&R for determining the bearing (azimuth) between the stations relative to true north, although the FCC's fill-in form does provide azimuth. My problem with using the fill-in form is that I have hundreds of computations to make and...

Arrgh!..Need OW Help Please
Let me preface by saying I am using this document http://www.msexchange.org/tutorials/OWA_Exchange_Server_2003.html as a guide. I have not configured any SSL options yet because I can't get it to work. Trying to build Exchange 2003 Frontend/backend on my LAN. Front end is W2k3 SP1\Exchange 2003 SP1 and Backend is W2k3 SP1\Exchange 2003 (no SP1). After installing Exchange on the 2nd server (to be the new Front-end), I am able to create a mailbox in it's own store and access it via OWA from a Win2000 Pro workstation. This mailbox resides on the 2nd server's own store. I then en...

How to pass value from one form to another form.
I have the following code in BookList.aspx: <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="LibSysDataSource" AllowPaging="True"> <Columns> <asp:BoundField DataField="AccessionNo" HeaderText="Accession No" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title&qu...

field returns a value if at least one matching record is found
I had posted this question in the Forms thread, but that was probably the wrong place. TblPartNumInfo TblDefectRpt My query for PartNumInfo brings together lots of different tables including customers, contact info, status of jobs. A feature I need to add is a warning if at least one defect report exists for a given part number. It would be shown on the form as a conditional format of the text box where the part number is shown. So I would like to add a field to my query that would return some value (like the part#ID) if at least one Defect Report exists for a part#. I created a sepa...

Omit data based on criteria in reports
I have a report which gives an employee's productivity rating, based on a set standard. This standard depends on what product the employee is running at the time. However, some times the employees run products that do not have standards associated with them (trial products for instance). A daily report tells: Employee Product How much was produced How much was expected (standard) % Expected (How much was produced/How much was expected) When a trial product is run on a given day, the % Expected number is Null (=IIf([How much was expected] = 0, Nul...

CSocket.Connect() Port Value
All: What is the best way to determine the "Host Port" value in the CSocket.Connect() function? The examples use the value 700 or greater. Can there be a conflict if I just use the value 700? -- Regards; Bruce Kingsley The correct port value to use is the port number of the port you are trying to connect to. That is determined by your host. If you are writing the host, unless you are implementing a standard protocol, it should not be < 1024. It must not be hardwired unless you have a registered port number (which you must get from IANA, the Internet Assigned Number Autho...

Field values used to filter a report
I have a report that is based upon a query that filters the data in a variety of ways. The net result is a listing of Zip Codes that I ultimately use as a part of the data for a report. The contents of this report is limited to the Zip Codes from the query. (Not a true filter) I need to be able to print this list of zip codes in a comma seperated list in the report header. I am sure this can be easily accomplished but for the life of me I cannot figure this out. Any help would be greatly appreciated. Thanks in advance. There are 2 parts to your question: a) How to provide an interface ...

The lookup reference could not be resolved
Hi, I am trying to use the Data Migration Manager to do a very simple import of two entities (one of which has a relationship with the other). No matter what I do, I am consistently getting the message "The lookup reference could not be resolved" even though all of the values I am trying to relate exist in the related entity file I am also importing. The scenario is very simple: EntityA - Column 1 - Column 2 - Column 3 EntityB - Column 1 - Column 2 [relationship; EntityB.Column2 = EntityA.Column2 (picklist)] - Column 3 - Column 4 Does anyone have any ideas? This has bee...

display last value in a column
I would like to display the last value unequal to zero recorded in a column in a specific cell. For example, A1 is to display the last value unequal to zero recorded in column B. The number of entries and values in column B changes frequently; therefore the last value in column B is in a different row. Thanks. -- Mike Try this... Assuming there are no logical TRUE entries in the range. =LOOKUP(1E100,1/B2:B100,B2:B100) -- Biff Microsoft Excel MVP "Mike" <Mike@discussions.microsoft.com> wrote in message news:DFFCBDB0-3632-46AC-A3AB-073E6F0D475...

Need helps about importing and etc.
I'm a newbie, where can i find information about RMS Setup and Deployment Overview, Installation Requirements, Terminology, Upgrading, Configuration, Import/Export, Limitations, and so on? thanks, Jay K Partner Source http://www.microsoft.com/BusinessSolutions/partnersource.mspx -- Mobitech Lady Amy Luby Mobitech 402.330.0707 www.mobitechonline.com "JayK" <JayK@discussions.microsoft.com> wrote in message news:3AD41CE8-6B86-45A0-A2EA-A61A91CFB482@microsoft.com... > I'm a newbie, where can i find information about RMS Setup and Deployment > Overview, Instal...

Two charttype on chart
Hi, I have export the data to excel from Vb, then add the graph in excel and set my secondary axis as.. ..SeriesCollection(4).AxisGroup = xlSecondary then how we set this group only as chatrtype as xlLine and the primary axis values by Column charttype -- Message posted via http://www.officekb.com Hi, Try this, ..SeriesCollection(4).AxisGroup = xlSecondary ..SeriesCollection(4).ChartType = xlLine Cheers Andy shah firdaous via OfficeKB.com wrote: > Hi, I have export the data to excel from Vb, then add the graph in excel > and set my secondary axis as.. > .SeriesCollection(4).Axi...

Count rows based on multiple criteria
I have a need to count the number of rows in an Excel spreadsheet based on contents of multiple cells. The formula is on one worksheet and all data is on a second worksheet. IE: count all rows that have the word "Windows" in column B and the word "Complete" in column G. Have not been able to figure this out with common functions like count, countif, dcount, etc. Help appreciated. -- DMG IT Professional Search for SUMPRODUCT or SUMIFS if you use XL-2007 "dmg" wrote: > I have a need to count the number of rows in an Excel spreadshee...

two servers for two domains
i have two domains, the first is 2000 and the second is NT. the exchange server is installed on NT. all the users (of the domain NT and 2000) are installed on a exchange server in the NT domain. i would like to have two servers exchange, one for the users on NT domain and the other for the users on 2000 domain, but with the same suffix (i mean "@microsoft.com") and with the server exchange on NT who goes on internet for the external email. how i can do this? thanks XCON: Sharing SMTP Address Spaces in Exchange 2000 PSS ID Number: 321721 cinqueg.nospam@yahoo.com wrote: > ...

Values
I would like to add 2 values (value and percent) to my column bar chart. I need to see my raw N and the percent on one bar. Anyone know how to do that? As an example, set your data up as follows assuming the below: (1) The letters are in column A (2) The numbers are in column B (3) The percentages are in column C a 4 25% b 3 19% c 4 25% d 5 31% Create a Clustered Column Chart. Double-click on the columns and in the Format Data Series dialog box go to the Data Labels tab and check “Label contains Value”. When complete, you should have data labels for all of the bars. Again, in the...

Cancel button's ActiveControl reflects as value of text box
Hi All, On my form I have an "on exit" code to test whether its text box is "empty" or not. When I click the "Cancel" button I still get the msg that the txt box is empty. Using Debug.print to show the Me.ActiveControl gives me the value of the preceeding txt box. What I'm trying to achieve is to ignore the "empty" test if cancel has been selected by exiting the "on exit" before the msg box sub & clearly I'm misunderstanding the usage here somewhere. Hugh I'm a bit unclear what you want to accomplish... ...

Need to change pic viewer in Excel
When I was running XP Home I could open a link to a picture in Windows FAX viewer. But in running XP Pro the picture opens in IE which causes multiple browser windows to close if viewing several linked pictures. This is an Excel newsgroup. You should post your question to a WindowsXP Pro newsgroup. HTH Otto "rgroff" <rgroff@discussions.microsoft.com> wrote in message news:46469ECB-F3B4-4F33-9FAC-8496239F14C2@microsoft.com... > When I was running XP Home I could open a link to a picture in Windows FAX > viewer. But in running XP Pro the picture opens in IE which...

Problem formatting date values in chart...
I have a blood pressure tracking spreadsheet -- at a minimum, it tracks Systolic and Diastolic pressure for a given day. A B C E.g.: Date Systolic Diastolic 1 9/3/04 120 90 2 9/4/04 123 87 3 9/5/04 129 79 I currently have a simple line graph that shows systolic data points over diastolic data points. The left hand (Y axis) value is "Pressure". My problem is that I want the "date" values of Col. A to display at the bottom of the graph as a label with each corresponding data point and...

Field name derived from a string value returns a Run-time error 2465.
I have two labels on a form and want to populate them during the run time of my form. The name of the labels are "1" the other is called "2" (without the quotes of course). Field 1 runs OK but two returns a run-time error 2465. Of course, the real form is much bigger. A lot of fields called 1 through 31 are populated from within a loop. To simplify my question I down sized this post to two label fields. Any one know how to solve the problem with label "2"? Any help or hint is appreciated. ' This works OK Me![1].Caption = "abc"...