Returning Data from a third cell in same row that meets two other

I'm looking for a formula that will return a result from a cell on a same row 
as two other cells that meet certain criteria.  I'm sure there is a way to do 
this but i am a novice at this kind of stuff and can't seem to figure it out.

For example i want a cell to = what is in column E when column A="36751" and 
when column B="Total Returns"  The spreadsheet has 55000 rows.  there will 
only be one instance where both these criteria are met.  i want to use this 
to create a seperate spreadsheet with just info i need and can update on a 
daily basis.

thanks in advance for helping me out.
0
USChad (4)
12/15/2004 5:13:04 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
456 Views

Similar Articles

[PageSpeed] 25

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=INDEX(E1:E55000,MATCH(1,(A1:A55000=36571)*(B1:55000="Total Returns"),0))

-- 
Regards
Frank Kabel
Frankfurt, Germany
"USChad" <USChad@discussions.microsoft.com> schrieb im Newsbeitrag 
news:C5C508BB-9DAB-4742-8FC4-548E232E35CA@microsoft.com...
> I'm looking for a formula that will return a result from a cell on a same 
> row
> as two other cells that meet certain criteria.  I'm sure there is a way to 
> do
> this but i am a novice at this kind of stuff and can't seem to figure it 
> out.
>
> For example i want a cell to = what is in column E when column A="36751" 
> and
> when column B="Total Returns"  The spreadsheet has 55000 rows.  there will
> only be one instance where both these criteria are met.  i want to use 
> this
> to create a seperate spreadsheet with just info i need and can update on a
> daily basis.
>
> thanks in advance for helping me out. 


0
frank.kabel (11126)
12/15/2004 5:19:39 PM
One way

=INDEX(E2:E100,MATCH(1,(A2:A100=36751)*(B2:B100="Total Returns"),0))

entered with ctrl + shift & enter

you can replace your 2 conditions with cell references where you would pur 
rhe conditions, that way you don't have to edit the formula every time you 
change conditons


Regards

Peo Sjoblom

"USChad" wrote:

> I'm looking for a formula that will return a result from a cell on a same row 
> as two other cells that meet certain criteria.  I'm sure there is a way to do 
> this but i am a novice at this kind of stuff and can't seem to figure it out.
> 
> For example i want a cell to = what is in column E when column A="36751" and 
> when column B="Total Returns"  The spreadsheet has 55000 rows.  there will 
> only be one instance where both these criteria are met.  i want to use this 
> to create a seperate spreadsheet with just info i need and can update on a 
> daily basis.
> 
> thanks in advance for helping me out.
0
PeoSjoblom (789)
12/15/2004 5:23:02 PM
thanks for your help. for some reason i still can't get this to work. i've 
been playing around with it all day.  it seems to get hung up on the second 
part when it looks up "total returns" it wants to locate some other info in 
one of the other cells and returns a "false" for that part of the formula.  
any other ideas would be appreachiated.

thanks


"Peo Sjoblom" wrote:

> One way
> 
> =INDEX(E2:E100,MATCH(1,(A2:A100=36751)*(B2:B100="Total Returns"),0))
> 
> entered with ctrl + shift & enter
> 
> you can replace your 2 conditions with cell references where you would pur 
> rhe conditions, that way you don't have to edit the formula every time you 
> change conditons
> 
> 
> Regards
> 
> Peo Sjoblom
> 
> "USChad" wrote:
> 
> > I'm looking for a formula that will return a result from a cell on a same row 
> > as two other cells that meet certain criteria.  I'm sure there is a way to do 
> > this but i am a novice at this kind of stuff and can't seem to figure it out.
> > 
> > For example i want a cell to = what is in column E when column A="36751" and 
> > when column B="Total Returns"  The spreadsheet has 55000 rows.  there will 
> > only be one instance where both these criteria are met.  i want to use this 
> > to create a seperate spreadsheet with just info i need and can update on a 
> > daily basis.
> > 
> > thanks in advance for helping me out.
0
USChad (4)
12/16/2004 3:53:05 PM
Reply:

Similar Artilces:

Can I use "find / replace" to automatically "Insert Rows" in a do.
Have a long column of numbers Every time I see the number 5, I need to insert two rows. Doing this by hand is tedious. Can you help me find a shortcut? The IF function does not do this, right? Not 25, 35, 5351, just plain old 5? Option Explicit Sub testme01() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("sheet1") FirstRow = 1 LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 If .Cells(iRow, "C").Value = 5 Then ...

Pivot Table not pulling all fields or data
Hi All........ Without getting too verbose, I have a 900 row by 26 column database I am applying a Pivot Table to. The code seems to run fine, but it is not returning some of the fields, and of course the related data. I have tried reformatting the entire columns I am pulling from but no joy......however, when I reformat an errant cell from General to TEXT, that row comes in........and/or, if I change the value in one of the missing cells, it will bring that row in. The code runs fine, and was mostly derived by recording a macro in XL2k, but the data cannot be trusted becau...

How do I automatiicaly redefinie Source data range for a chart?
I have A Chart Defined by Category (X) values in Collumn B and Y values in Collumn C. Right now Both are defined by rows 4 to 114. I would like to be able to have the chart automatically redefine itself so if I put a value in C115 the chart would automaticaly set Y value range to be C4:C115 eithout having to manually define chart. Is this possible? If so how? I've tried OFFSET bu so far no luck. Hi, If you turn your data cells into a List, or Table in xl2007, the chart will auto expand. The named range approach will work if you set up the names correctly. See here for more info...

Exchange data backup
I'm worried emails backup of the small office. Does SBS2008 Exchange server has a direct backup capability of whole mail database? How to backup every night Emails from server to external harddisk? Does Exchange server needs to be stopped before backup? Thanks SBS2008 Takes care of it. http://blogs.technet.com/sbs/archive/2008/11/03/introducing-sbs-2008-backup.aspx look at the comments section Russ -- Russell Grover - SBITS.Biz [SBS-MVP] MCP, MCPS, MCNPS, SBSC Microsoft Certified Small Business Specialist 24hr SBS Remote Support - www.SBITS.Biz Question or Seco...

Data Save error
GP Ver 6.0. Recently I am facing an error with normal users unable to save records, where as the system administrator is able to save the same information. Please advice on this error, what has to be done. I have disabled the windows user ID of the Database owner. Thinking of this made Dynasa as the DBO. Database is having enough free space available. Hi How big is the company database? Is it over 10GB? Regards James "Matthews" <Matthews@discussions.microsoft.com> wrote in message news:3FF3F553-5B18-4D8A-B65E-8A253BF94BB2@microsoft.com... > GP Ver 6.0. Recently I...

Returning Nil
Hello I have a timesheet in excel, I would like the final column to work out the overtime per day which is over 7 hours. I've entered the formula: =I6-$H$4 I6 is the cell that has the total hours worked per day, in decimals. ie 7.75 H4 contains Number 7. And this works ok. BUT if the timesheet hasn't been filled the formula returns -7 What is the formula that returns nil or null if the row hasn't been filled? BTW in I6 there is the formula: =HOUR(H8)+(INT((MINUTE(H8)+7)/15))/4 Thank you Jen =MAX(I6-$H$4,0) HTH, Bernie MS Excel MVP "JB" <somehow@somewhere>...

Getting a filename into VBA and putting it into cells
Basically what I have is a button that creates a new column that should be full of linked values. When the user hits the button, he is asked to select a file from his hard-drive; the code then takes the name of that file and creates a number of links in the new column. So for example: 1) User hits button and selects the closed excel file C:\Tempfiles\Testsheet.xls 2) Excel creates the new column and populates it with formulas that link to the file; in A1 it enters ='C:\Tempfiles\[Testsheet.xls]Overview'!$A$1 in A2 it enters ='C:\Tempfiles\[Testsheet.xls]Budget'...

Finding intersection point in rows and columns
I am trying to work a macro which will do the work for me (go figure). Here is the easy example. I have a worksheet which has a 3 columns an a bunch of rows: the column headings are " ", "total", "add-on" the rows then consist of "mike","1,000","200"... then next ro "cindy","1,230","23" etc I would like a different worksheet to have a macro button that ca take the data and put it into a new table and stuff. I was thinking along the lines of creating labels on the top and left and then referencing the ...

PO Return Distribution Accounts
Upgraded to GP 9.0 two months ago; have recently noticed that when doing a PO Return transaction (inventory w/credit) for an item that is in a Quantity Type = Returned, that the system will use our Inventory Offset account as the default "PURCH" distribution account; before the upgrade our Inventory Returns account was used. Is this an intended change or do we have a posting account setup issue? We have not made any changes to Posting Accounts, Class ID Accounts, etc. to my knowledge, this is the way it worked in prior versions (8, 7.5). It the PO return transaction takes...

Auto moving cells
We use Excel 2002 XP at work, and im trying to find out if its possible to get excel to auto move a cell to the next column. The spread sheet is setout with 5 columns and is about 20 rows down. Each cell has a number in it (which is the number of each till in the store). This gets change quite a few times each week and as they are all in alphabetical order, if a number needs adding or removed, we have to keep doing it buy insert shit cells down etc, then the problem is since its only 20 rows down 1 cell will move on to row 21. Would it be possible to make excel auto maticall move this cell...

cell range not changing when refreshing linked data- sumproduct fo
The cell range is not updating correctly to reflect the last row in the data sheet that is being refreshed. 5878 is the correct number of rows and 5824 is not. I can find and replace in my formulas to correct the problem, but it does it each time the data is refreshed. Any suggestions as to why? =(SUMPRODUCT((Expense!$B$2:$B$5878=$C$2)*(Expense!$C$2:$C$5878=$C$3)*(Expense!$W$2:$W$5824=$A6)*(Expense!$M$2:$M$5878))) I gave you the INDIRECT option y'day, but received no feedback from you in that thread. Looks like you're more interested in knowing why? One simple hunc...

Data vs. forms & Unload/reload questions
A .mdb file seems to encompass both the database and all the 'code' (forms, modules, and logic) used to CRUD the data in the tables. 1) Is there any way to separate the database from the code into separate files, eg. the db is mydata.mdb and the 'code is in a separate file, eg. code.mdb? 2) Say you have v1.0 of an app and the tables and attributes are populated with data. You then go and build v2.0 of the app, adding tables and columns to what already existed in v1.0, and perhaps moving some of the v1.0 attributes into different tables. What is the recommended way to unload/reloa...

Data Format: how can I re-format
The spreadsheet that was exported for me to use has the information in paragraph form: It is customer ID name and billing info but it is like a long series of address labels. I want to be able to have them listed under captions like company name, contact, address, city, etc.. so I can use the data. There is an empty row between each entry. Each entry is 3 or 4 rows. There are about 500 records so i dont want to manually set them up. Thanks Try tinkering around with these steps Assuming your list is in col A, data in A2 down (If data starts in A1 down, insert a new row for the co...

Return a value when Date x is between Date y and z
Hi, I have a table something like this: A B C 1 From To Result 2 01/07/2009 01/07/2010 0-1 3 01/07/2010 01/07/2011 1-2 4 01/07/2011 01/07/2012 2-3 5 01/07/2012 01/07/2013 3-4 In another column (X) I have a list of Dates and I want return the "result" from the row where the date lies between. So for example in X3 I have 16/10/2011 and I can see this date is between the 2 dates in row 4 in the table above so I want to return "2-3" I've tried an array formula ...

Cell Reference to Page Header
Is is possible to reference a cell in the page header? I am hoping to have a sheet where demographic information can be inputted. Name, Address, etc. Then have that demographic information show in the header of every sheet printed. Hi only possible with VBA, not with a cell reference. -- Regards Frank Kabel Frankfurt, Germany Allen wrote: > Is is possible to reference a cell in the page header? I > am hoping to have a sheet where demographic information > can be inputted. Name, Address, etc. Then have that > demographic information show in the header of every sheet &...

Display comments as data #2
thanks a lot , this worked exactly as required, the comment go converted to data Now just one more thing . is the vice versa possible i.e if i have dat in column A can it be shown as comment in column B. the reason i need this is bcos i am shifting some data with thei comments to MS access(Hence the need to convert omments to data) processing them and again converting them to excel (the need to conver data back to comments -- sonik ----------------------------------------------------------------------- sonika's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=...

Count invalid data entries
I am using the following macro to identify invalid data entries in cells with data validation applied. Sub CheckOrder() ' Application.CommandBars("Formula Auditing").Visible = True ActiveSheet.CircleInvalid Sheets("Configuration").CircleInvalid Sheets("Parts_TakeOff").CircleInvalid MsgBox ("Check for Red Circled Invalid Data Entries on" & Chr(10) & " Configuration and Parts_TakeOff Sheets") End Sub Is there a way to count the number of invalid data entries (red circles) and write the number to a particular ce...

Sorting and Header Row's
We work with a xls document on a daily basis, and the user's perform a repeated sort function on that document multiple times a day. Appearently, Excel used to save what you last sorted for and would also leave the "Header Row" circle checked, after you closed the app... Now, neither is saved... So I am just wondering if there is a way to have excel remember what was sorted last, how it was sorted and to sort it with a header row with out having to reset all those settings every time you do a sort...? Thanks! What version of Excel are you running? I have 2003 and it seems...

ROWS Function
In Office Help, there is an example of the FOWS Function. In B2, the fumula is : "=ROWS(C1:E4)" I believe this one is easy to understand. However, another fumula in cell A3 said: =ROWS({1,2,3;4,5,6}) The description about it is "Number of rows in the array constant". The result is "2". Could you please explain it in a easy way for me to understand? Thanks. the example mentions 2 arrays. the rows function is supposed to retur the number of rows in any given array. first: "=ROWS(C1:E4)" C1 D1 E1 C2 D2 E2 C3 D3 E3 C4 D4 E4 this is a 4 x 3 matrix, 4...

Getting row reference number
Kindly advise me how to get the row reference number of data which VLOOKUP function fetches. Thanks If you use the MATCH function, instead of the VLOOKUP function, it returns the row_index of the searched range. Example: if you have A, B, C in cells A11:A13 and you use =MATCH("C",A11:A13,1) that formula will return 3 (because A is the 3rd item in the list. To get the actual row number, use: =MATCH("C",A11:A13,1)+10 (because you need to add in the number of rows above cell A11) Does that help? *********** Regards, Ron "Mahendhra" wrote: > Kindly ad...

forms not listed in database contents, yet can add data to them
Hi - there are no forms listed when I open up the database in design view. All the tables, queries, and macros are there. When I open up the database to add data, all the forms are there and can be used. The data is stored. So, it seems that the forms are still there, but just cannot be seen in design view. The database, when I open it up in design view, says Access 2000 file object even though I am using Access 2003 - could that be the problem? Thanks! You cannot open an entire database in Design View. Try opening it normally, selecting a form, and choosing Design View from the V...

can IF leave a cell EMPTY ??
I'm attempting some simple (to me at least) data analysis and am running into trouble with zero values in the data array (region). These zero values (null string: "", actually) represent non-existant data that I'd like the chart to ignore, but as the array actually contains formulas the chart engine does not see an empty cell. Is it possible to use IF to cause a cell to appear Empty (ie, IsBlank(cell) == True) ? Something like: " = if (condition, reference, Empty) " My raw data consists of date shipped and date recieved; I have created an array (Mon, Tue...

copying non adjancent cells
sorry Ms. debra dalagliesh I missed your reply to my query. I got it later through google groups search. thank you very much for clarifying my doubts. I am repeating your message for the sake of readers seeing only this messagae copy of Ms. Dalgleish's message from: Debra Dalgleish (dsd@contexturesXSPAM.com) Subject: Re: copying non adjacent cells If you want to copy multiple ranges, the selections must match in rows and columns In each column, the same rows must be selected, or nothing is selected In each row, the same columns must be selected, or nothing is selected For example, you...

Data validation doesn't work if contents is added by pasting
Hi I've data validation set so that only the number 1 can be entered, but even this cannot be entered if an adjacent cell is a certain value. this works for direct entry, but not if the value is pasted in or added using the "drag handle". Is there any way to stop this happening as I just know somewones going to try and fill all the cells by draging the values down. Regards Jeff Jeff, You could disable "Allow cell drag and drop" in Tools | Options or... copy the following code, right click the sheet tab and choose 'view code'. Paste the code into the modu...

How to get Preview Pane in separate Data File in Outlook 2002
For back-up and portability purposes, we use separate Outlook Data Files. (File, New, Outlook Data File, ....) Then we use the Organizer Wizard to move files from the Inbox to the appropriate data file. All of this works great. The only thing we can't figure out is how to turn on the preview pane on one of these files. It works fine on the standard InBox or any Personal Folder that is in the main *.PST file. We use the described method (View, Current View, Other Settings, check by Preview Pane) and we don't get any error message. However, we also do NOT get ...