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
485 Views

Similar Articles

[PageSpeed] 16

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:

row reproduction
i recently downloaded the "Bond Amortization" template from they template page (so i like the templates. is that bad?) and i was intruiged as to how the workbook created and removed rows automatically depending on, in this case, how long it would take to pay off the bond. can i duplicate this? i would like to modify a stock investment calulator so it will shorten/lengthen depenent on how many stocks they user wants to view at a time. any help? -- Brought to you by Pringles and his infinite genius. ~''~ No, templates aren't bad. Look at the formulas. They d...

Running two files simultaneously....
Can you run two MS Money files at the same time? I have one for personal use and one for business use, but am always having to close out of one and open the other. I'd love to be able to have a window for each open at the same time in order to view information.....MS Money 2001 Simultaneously? No. "william" <turnerinsurance@epride.net> wrote in message news:4c5a01c490f1$37b36bc0$a501280a@phx.gbl... > Can you run two MS Money files at the same time? I have > one for personal use and one for business use, but am > always having to close out of one and open t...

deleting a row with macro
NOOBIE here... Here's what Ive got. I am trying to create a "Delete Vendor" button I know there is a much better way to do this.. On one sheet is the linked cell of a combobox. I am selecting that cell, copying and switching to the data sheet. There I want to find the copied cell's value, select that complete ro and delete it. (Only the content, not the row itself because I wil have a sort run afterward and it is an array so I want to keep th size.) My problem is that "Rows(ActiveCell.Row).Select" is executing befor the find dialog box has time to appear effecti...

Importing Data into Existing Pivot Tables ?
Can anyone tell me if it is possible to *import data into an existin pivot table *(possibly from a excel spreadsheet(refresh) or with query to a Access database table) **I need to do this daily with pivot table if possible ! Thanks in advance ! J -- jman55 ----------------------------------------------------------------------- jman559's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2945 View this thread: http://www.excelforum.com/showthread.php?threadid=49157 ...

Forms: can a cell itself be clickable?
Hi! I'm trying to create a form for my boss. I'm using the forms toolbar, but what it does is throw in a check box for instance, but if I change the cell in any way, the "picture" of the click box stays where it's at. Any way to change this? In other words, I don't want to the click box to be a picture, I want it to be an integral part of the cell, so I can modify it as needed (ie. place it centrered horizontally or vertically etc. Am I dreaming in colour? Thanks! Carmen I've seen a macro that will insert a checkmark when you select the cell, but I&#...

Query: Link data between worksheets?
I'm trying to find a shortcut to save me some time. This is the problem: I'm setting up a master worksheet, and 30 to 40 "slave" worksheets tha need to run off it (all within the same workbook). The slave worksheet all retrieve data from the master. The data is setup to run across i rows (i.e. Row 1 contains 1 record). Each record has an individua identification number entered in column A. Is there a way that I can tell the slave sheets to copy an entire row based purely on the identification number entered in column A? Thanks in advance if anyone can help, as this could ...

Data Validation
Hi all I want to set data validation on a cell so that it will accept the following (and only the following) text strings: (1) "BR" (literally) or (2) "NT" (literally) or (3) "nA" where n is any positive integral numerical value including zero and A may take any of the values "L", "P", "T", "V" or "Y" or (4) "An" where n is any positive integral numerical value including zero and A may take either of the values "K" or "D". Is this possible, please, and if so how? thanks -- Return e...

Filtering data on subforms
How can I filter data in a subform? The subform is a datasheet linked to a parent form (field) thanks! Alberto R. On Fri, 8 Jun 2007 22:37:14 -0500, "Alberto Rios" <albertorios1@aol.com> wrote: >How can I filter data in a subform? >The subform is a datasheet linked to a parent form (field) Base the subform on a Query selecting the records you want to see. John W. Vinson [MVP] ...

Pictures in Excel cells
Dear friends I want to create a book with personal descriptions, including pictures. But how can I get a picture in an Excel cell? I thought that =HYPERLINK("file.jpg") would be the thing, but the pictures are not retrieved when I combine the Excel sheet to the final Word file. Please help. -- Jos´┐Ż Do you object if ... Alt > i > p > f and select the picture what you want to import. HTH -- MRT "Jose" <jose@127.0.0.1> wrote in message news:e4nsg59mm09cuqgr2etqtm06jm87idk343@4ax.com... > Dear friends > > I want to create...

How to lock cell
Hi, How to lock a particular cell without locking the whole work sheet Pls help me. -- Moideen Moideen;1599749 Wrote: > Hi, > > How to lock a particular cell without locking the whole work sheet > > Pls help me. Do you mean have one particular cell that cannot be updated/altered but the rest can? Or do you mean have a locked cell but without protecting the work sheet? -- Spencer101 By default all cells on a sheet are locked when the sheet is protected. Select all cells(CTRL + a...........twice in Excel 2003) then go to format>cells>protection and unlo...

Header and cell referencing
My issue is this: I have 6 sheets. The first sheet contains the clients information. I want to take the last name of the client from the first sheet and have it appear in the header of every sheet after it. How do I create a cell reference inside a header? David David Sub Path_All_Sheets() Set wkbktodo = ActiveWorkbook For Each ws In wkbktodo.Worksheets ws.PageSetup.RightHeader = Sheets("Clients").Range("A1").Text Next End Sub Assumes client's name is in A1 Note: you can do the same thing by right-click on first sheet tab and "select all ...

cell content into a note
hey guys, just curious if this can be achieved or how it can be achieved say i have this Column A Row 1 = Short Description Column B Row 1 = Long descriptoin B2:B10 = are all the short descriptions B2:B10 = are all the long descriptions is there a way to just put the comment into the short description cells from the long description cells via a note and then hide the long description column? can anyone advise as to how this can be achieved? cheers Don't multipost. You could add Comments to the column A cells with the text from Column B cells and have Column B hidden. Sub Comment...

Cell Number Format
Hi, when i type for example this any number with more than 15 digits, the first 15 digits appear like i wrote it but the rest of them are changed by zeros. EX: i write this 123456789123456789 and the number that is kept is 123456789123456000 Does anyone experienced this and knows how to resolve it. Thanks, Ricardo As you can verify in XL Help ("Specifications"), XL is limited to 15 decimal digits of precision for numbers. If you're entering, say, a credit card number, which doesn't require any math to be performed on it, you can preformat the cell as Text or prefix ...

Move to match cells
My spreadsheet looks like this: A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 00...

i meet a problem that how can i free the object .
I am programming a OPC client to get data from OPC Server. i meet a problem that how can i free the object . Firstly i create connectionpoint, then i release connection. --------------------------------------------------------------- create program list: IConnectionPoint* pConnectionPoint; IUnknown* ppUnk = NULL ; hResult = pGRPCPC->FindConnectionPoint(IID_IOPCDataCallback,&pConnectionPoint); CDataCallbackSink* pDataCallbackSink=new CDataCallbackSink ; pDataCallbackSink->pDoc = m_pOPCDoc ; pDataCallbackSink->ExternalQueryInterface(&IID_IUnknown,(void**)&ppUnk); DW...

Nonadjacent Cell Text Overflow
Is there a way to merge nonadjacent cells for text overflow. What I' trying to do is have text entered into a row of merged cells(e.g. Row A1:F1) but if the text is more than the cells can contain to overflo into another nonadjacent row of merged cells(Row3 A1:F1). Thanks fo any help with this -- Db171 ----------------------------------------------------------------------- Db1712's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27498 ...

Formatting Phone Numbers in a Merged Cells...
If I want to merge a "Company Name", "Phone Number" and a "Fax" number into 1 cell (for use in a drop down box), is there a way to maintain the 10 digit format for the phone/fax numbers? The phone/fax #'s appear as a string of 10 digits when they are merged, and are hard to read. Currently using a formula like this: =A3&" Phone: "&B3&" Fax: "&C3 I am tired of trying to get it to work, and can't seem to find the answer here. Thank you in advance! One way: =A3 & TEXT(B3,""" Phone:...

Two cells divided into next cell
Hope this makes sense. I have F column that totals down to F16. Then I have a total on D16. I need both totals to be divided and show up on G16. Every cell has $ Amts, but the only cells not totaled down the row are E,G and J. So G16 is blank. Is this possible to do and If so how. I don't even know the formula to Divide and I need this to automatically happen every time I Insert the Amts in the cells. Please help In G16: =D16/F16 "Day" <Day@discussions.microsoft.com> wrote in message news:62C49FCC-273A-425B-9EEF-DCF2109A0DD9@microsoft.com... > > ...

Macro to copy cell down
I want to create a macro that will copy the cell above to the active cell. OR the built-in key that will do that tenbob@optonline.net Bob A few seconds with the macro recorder set to "Relative Reference" gave me this. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 11/3/2003 by Gord Dibben ' ' Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub Gord Dibben XL2002 On Tue, 04 Nov 2003 04:26:28 GMT, Bob K. <tenbob@optonline.net> wrote: >I want to create a mac...

Automatically adjust table row height in Publisher
Is it possible to have Publisher automatically adjust row height when working in a table? No... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "cdavetype" <cdavetype@discussions.microsoft.com> wrote in message news:AAADE565-0540-4135-B7EF-D6DB4AD01B91@microsoft.com... > Is it possible to have Publisher automatically adjust row height when working > in a table? ...

get data form cells sequentially
Hi there, I have a spreadsheet i am creating which is giving me some amount o hassle!!! I'm sure it is possible to do this but i just cant figure ou how:- I have one worksheet which has many copies of the same table, eac table is consistantly ontop of the other i.e. first table uses cells A - G10 (a rectangle of cells). The dates i am trying to use are in a pattern of a nine row seperatio i.e. first date is in cell G2, next one is G11, next one G20, and s on. I need to find out how i can make a formula which i could copy whic would grab the date out of these cells in sequence for pl...

Insert string into a Cell ?
Hi, How do I insert string into a cell ? Dim myString As string myString = "abcd" shp.CellsSRC(visSectionProp, visRowProp, visCustPropsValue) = myString 'This will yield with type mismatch error. Thanks. Hello m, You've got a reference to the cell but not the property of the cell object. So if you want to set the cell's formula you need to use that property. Also, as you're adding a string you need to wrap it up in quotes (note two double quotes in a row get evaluated as a single set of quotes). Have a go with the following: shp.CellsSRC(visSectionProp, ...

Dividing a cell in Excel 2000
I am embarrassed to ask, but here goes. About a year ago some of you on this forum explained how I can divide a cell without creating a whole new row. I can't remember how I did it. With a diagonal line? A horizontal line? A vertical line? Doesn't really matter; I just want to enter a number on each side of the divide. Much appreciation, Lois You can always find previous posts in the archives. Just enter your name or email address in the author field at http://groups.google.com/advanced_group_search?q=group:*excel* to see your posts and any replies. You might take ...

How to quote cells when "IF" word is involved
On Sheet1, I have a data set (C1:C100) based on the input of cell A1. When A1 has various method choices,say,"method 1","method 2","method 3", each cell in the data set will have a new value accordingly. (=IF(A1="method 1",formula1,IF(A1="method 2",formula2,formula3)) I want to quote those three data sets on Sheet2. A lousy way is I could just duplicate Sheet1, set A1 to different value and quote data sets from different sheets. Is it possible to quote three data sets simultaneously from Sheet1? Thanks, Ming Perhaps use a 2 variable data t...

how to automatically delete non-contiguous rows
Is it possible to define a large data field then delete non-contiguous rows, like every fifth row, without having to delete each row one at a time? Hi One way to insert a column with a formula and use SpecialCells(xlCellTypeBlanks) to delete the rows Test it on a copy of your workbook Sub test1() Application.ScreenUpdating = False Dim myRows As Long Range("A1").EntireColumn.Insert myRows = ActiveSheet.UsedRange.Rows.Count With Range(Cells(1, 1), Cells(myRows, 1)) .FormulaR1C1 = "=IF(MOD(ROW(),5)=1,""Keep"","""&...