Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to lookup 2 different dates in a table and if both of those dates appear on one sheet, I need the associated value to be returned. If this is possible, how would I do this?

0 |

3/23/2010 1:35:01 PM

Hi, An example will help but assuming that you want to extract a value from column C if the dates in column D and E match your dates entered in cells A1 and B1 and put the value in A2 use in A2 =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) "Sarah" wrote: > Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to > lookup 2 different dates in a table and if both of those dates appear on one > sheet, I need the associated value to be returned. If this is possible, how > would I do this?

0 |

3/23/2010 2:00:07 PM

Hi, What I am looking at cell A1 and B1 from Sheet1 and looking up those two dates in a table array on Sheet2. If both of those dates are found in lets say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to be returned on Sheet1. Does this help at all? "Eduardo" wrote: > Hi, > An example will help but assuming that you want to extract a value from > column C if the dates in column D and E match your dates entered in cells A1 > and B1 and put the value in A2 use in A2 > > =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) > > > > "Sarah" wrote: > > > Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to > > lookup 2 different dates in a table and if both of those dates appear on one > > sheet, I need the associated value to be returned. If this is possible, how > > would I do this?

0 |

3/23/2010 2:07:01 PM

Hi Sarah, try =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) formula will look what you have in cell a1 and b1 through columns B and C in sheet2 and when match is found will get total from column D Hope this helps "Sarah" wrote: > Hi, > > What I am looking at cell A1 and B1 from Sheet1 and looking up those two > dates in a table array on Sheet2. If both of those dates are found in lets > say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to > be returned on Sheet1. Does this help at all? > > "Eduardo" wrote: > > > Hi, > > An example will help but assuming that you want to extract a value from > > column C if the dates in column D and E match your dates entered in cells A1 > > and B1 and put the value in A2 use in A2 > > > > =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) > > > > > > > > "Sarah" wrote: > > > > > Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to > > > lookup 2 different dates in a table and if both of those dates appear on one > > > sheet, I need the associated value to be returned. If this is possible, how > > > would I do this?

0 |

3/23/2010 2:21:02 PM

Yes. Modify Eduardo's formula to fit your ranges and layout. Something like this: =SUMPRODUCT(--(A1=Sheet2!$B$1:$B$1000),--(B1=Sheet2!$C$1:$C$1000),Sheet2!$D$1:$D$1000) Sarah wrote: > Hi, > > What I am looking at cell A1 and B1 from Sheet1 and looking up those two > dates in a table array on Sheet2. If both of those dates are found in lets > say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to > be returned on Sheet1. Does this help at all? > > "Eduardo" wrote: > >> Hi, >> An example will help but assuming that you want to extract a value from >> column C if the dates in column D and E match your dates entered in cells A1 >> and B1 and put the value in A2 use in A2 >> >> =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) >> >> >> >> "Sarah" wrote: >> >>> Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to >>> lookup 2 different dates in a table and if both of those dates appear on one >>> sheet, I need the associated value to be returned. If this is possible, how >>> would I do this?

0 |

3/23/2010 2:22:28 PM

That didnt work. The return value is actually a text so a sum wouldnt work. Thanks though! "Eduardo" wrote: > Hi Sarah, > try > > > =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) > > formula will look what you have in cell a1 and b1 through columns B and C in > sheet2 and when match is found will get total from column D > > Hope this helps > > "Sarah" wrote: > > > Hi, > > > > What I am looking at cell A1 and B1 from Sheet1 and looking up those two > > dates in a table array on Sheet2. If both of those dates are found in lets > > say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to > > be returned on Sheet1. Does this help at all? > > > > "Eduardo" wrote: > > > > > Hi, > > > An example will help but assuming that you want to extract a value from > > > column C if the dates in column D and E match your dates entered in cells A1 > > > and B1 and put the value in A2 use in A2 > > > > > > =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) > > > > > > > > > > > > "Sarah" wrote: > > > > > > > Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to > > > > lookup 2 different dates in a table and if both of those dates appear on one > > > > sheet, I need the associated value to be returned. If this is possible, how > > > > would I do this?

0 |

3/23/2010 2:40:01 PM

The more information you provide up front, the easier it is to provide a working solution. Try this array formula (commit with CTRL+SHIFT+ENTER): =INDEX(Sheet2!$B$2:$D$1000,MATCH(A1&B1, Sheet2!$B$2:$B$1000&Sheet2!$C$2:$C$1000,0),3) Sarah wrote: > That didnt work. The return value is actually a text so a sum wouldnt work. > Thanks though! > > "Eduardo" wrote: > >> Hi Sarah, >> try >> >> >> =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) >> >> formula will look what you have in cell a1 and b1 through columns B and C in >> sheet2 and when match is found will get total from column D >> >> Hope this helps >> >> "Sarah" wrote: >> >>> Hi, >>> >>> What I am looking at cell A1 and B1 from Sheet1 and looking up those two >>> dates in a table array on Sheet2. If both of those dates are found in lets >>> say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to >>> be returned on Sheet1. Does this help at all? >>> >>> "Eduardo" wrote: >>> >>>> Hi, >>>> An example will help but assuming that you want to extract a value from >>>> column C if the dates in column D and E match your dates entered in cells A1 >>>> and B1 and put the value in A2 use in A2 >>>> >>>> =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) >>>> >>>> >>>> >>>> "Sarah" wrote: >>>> >>>>> Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to >>>>> lookup 2 different dates in a table and if both of those dates appear on one >>>>> sheet, I need the associated value to be returned. If this is possible, how >>>>> would I do this?

0 |

3/23/2010 2:52:03 PM

Actually, the column selection in the INDEX() is not necessary if we define the array as just column D: =INDEX(Sheet2!$D$2:$D$1000,MATCH(A1&B1, Sheet2!$B$2:$B$1000&Sheet2!$C$2:$C$1000,0)) Glenn wrote: > The more information you provide up front, the easier it is to provide a > working solution. > > Try this array formula (commit with CTRL+SHIFT+ENTER): > > =INDEX(Sheet2!$B$2:$D$1000,MATCH(A1&B1, > Sheet2!$B$2:$B$1000&Sheet2!$C$2:$C$1000,0),3) > > > Sarah wrote: >> That didnt work. The return value is actually a text so a sum wouldnt >> work. Thanks though! >> >> "Eduardo" wrote: >> >>> Hi Sarah, >>> try >>> >>> >>> =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) >>> >>> formula will look what you have in cell a1 and b1 through columns B >>> and C in sheet2 and when match is found will get total from column D >>> >>> Hope this helps >>> >>> "Sarah" wrote: >>> >>>> Hi, >>>> >>>> What I am looking at cell A1 and B1 from Sheet1 and looking up those >>>> two dates in a table array on Sheet2. If both of those dates are >>>> found in lets say cell B2 and C2 then I want the value in that table >>>> on sheet2, cell D2 to be returned on Sheet1. Does this help at all? >>>> "Eduardo" wrote: >>>> >>>>> Hi, >>>>> An example will help but assuming that you want to extract a value >>>>> from column C if the dates in column D and E match your dates >>>>> entered in cells A1 and B1 and put the value in A2 use in A2 >>>>> >>>>> =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) >>>>> >>>>> >>>>> >>>>> "Sarah" wrote: >>>>> >>>>>> Does anyone know if you can combine and IF, AND, and VLOOKUP. I >>>>>> need to lookup 2 different dates in a table and if both of those >>>>>> dates appear on one sheet, I need the associated value to be >>>>>> returned. If this is possible, how would I do this?

0 |

3/23/2010 3:05:16 PM

Hi, try =if(sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000))>0,sheet2!$D$1:$D$1000) "Sarah" wrote: > That didnt work. The return value is actually a text so a sum wouldnt work. > Thanks though! > > "Eduardo" wrote: > > > Hi Sarah, > > try > > > > > > =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) > > > > formula will look what you have in cell a1 and b1 through columns B and C in > > sheet2 and when match is found will get total from column D > > > > Hope this helps > > > > "Sarah" wrote: > > > > > Hi, > > > > > > What I am looking at cell A1 and B1 from Sheet1 and looking up those two > > > dates in a table array on Sheet2. If both of those dates are found in lets > > > say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to > > > be returned on Sheet1. Does this help at all? > > > > > > "Eduardo" wrote: > > > > > > > Hi, > > > > An example will help but assuming that you want to extract a value from > > > > column C if the dates in column D and E match your dates entered in cells A1 > > > > and B1 and put the value in A2 use in A2 > > > > > > > > =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) > > > > > > > > > > > > > > > > "Sarah" wrote: > > > > > > > > > Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to > > > > > lookup 2 different dates in a table and if both of those dates appear on one > > > > > sheet, I need the associated value to be returned. If this is possible, how > > > > > would I do this?

0 |

3/23/2010 3:13:01 PM

i think that worked. thanks! "Glenn" wrote: > The more information you provide up front, the easier it is to provide a working > solution. > > Try this array formula (commit with CTRL+SHIFT+ENTER): > > =INDEX(Sheet2!$B$2:$D$1000,MATCH(A1&B1, > Sheet2!$B$2:$B$1000&Sheet2!$C$2:$C$1000,0),3) > > > Sarah wrote: > > That didnt work. The return value is actually a text so a sum wouldnt work. > > Thanks though! > > > > "Eduardo" wrote: > > > >> Hi Sarah, > >> try > >> > >> > >> =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) > >> > >> formula will look what you have in cell a1 and b1 through columns B and C in > >> sheet2 and when match is found will get total from column D > >> > >> Hope this helps > >> > >> "Sarah" wrote: > >> > >>> Hi, > >>> > >>> What I am looking at cell A1 and B1 from Sheet1 and looking up those two > >>> dates in a table array on Sheet2. If both of those dates are found in lets > >>> say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to > >>> be returned on Sheet1. Does this help at all? > >>> > >>> "Eduardo" wrote: > >>> > >>>> Hi, > >>>> An example will help but assuming that you want to extract a value from > >>>> column C if the dates in column D and E match your dates entered in cells A1 > >>>> and B1 and put the value in A2 use in A2 > >>>> > >>>> =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) > >>>> > >>>> > >>>> > >>>> "Sarah" wrote: > >>>> > >>>>> Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to > >>>>> lookup 2 different dates in a table and if both of those dates appear on one > >>>>> sheet, I need the associated value to be returned. If this is possible, how > >>>>> would I do this? > . >

0 |

3/23/2010 4:05:01 PM

In Excel 2007 we can protect workbook using 2 ways: 1. Office button > Save as, choose file format, and then Tools, General options. 2. Office button > Prepare > Encrypt document Does anybody know is there difference between these 2 ways Thanks ...

Hi there, i saw the posting on regards "How do I give a lookup field a default value" . And i tried using the method posted there but still i'm not able to get my lookup edited with a default value passed in. i need a solution that can update or set the default value in the in lookup with the parameters that is being returned from the SDK. Regards angie Hi Angie, If you need to give the lookup a default value, Please look into using JavaScript populating it on Form Load. Please look into the CRM 3.0 SDK. If you found some of my code, I am happy to share with you. Darren Liu...

http://clixncash.com/ptp2.php?p=r&ref=price ...

Is it possible to use match to return value without reference to other cells? =IF(MATCH(H219,{"00","01","02","03","04","05","06","07","08","09"},0),<<return corresponding value>> ) where corresponding value should return "00" = Positve - Negative "01" = Positve - Positive "02" = Positive - Neutral "03" = Negative - Positve "04" = Negative - Negative "05" = Negative - Neutral "06" = Neutral -Positve "07&quo...

Hello Everybody, I have applied list validations to cell, say C4. The list contains two values : CY & CFS But when I copy & paste special values other than the list (sa WAREHOUSE from cell B4), excel accepts the same. Can anyone help me to restrict the cell updation only as per list. Thnks & Rgds, Rajendra -- Message posted from http://www.ExcelForum.com Hi with Data - Validation you can't prevent copy/paste operations. This would require VBA >-----Original Message----- >Hello Everybody, > >I have applied list validations to cell, say C4. > >The lis...

In my Office 2003 Picture Manager, when I right click a picture to send to mail or go to File, Send To recipient it goes to Outlook. I wish it to change it to Outlook Express. I have gone to IE, Tools, Options, Programs, and E-mail and changed the default to Outlook Express. My Office Word, Excel, and PowerPoint does not go to Outlook they go to Express, it only goes to Outlook in Picture Manager. ...

Is there a way to link columns in Excel, so that data that's entered flows from one column to the next? There's a function similar to this in Quark. Anybody know anything about this? Regards, If I understand the question correctly, and the information is being typed into the cells, you could try this: Select a block of cells, say A2:C10 Then, type a value. Press enter. If yuo press enter after each value entered, when you enter a value in A:10 and press enter, you're active cell will be B2. tj "M. Frazel" wrote: > Is there a way to link columns in Excel, so t...

I don't understand the calculations money makes to report these two measures. Can anyone enlighten me on what exactly Money is doing to get ROI and Annual %Return? Thanks. In microsoft.public.money, John Hidley wrote: >I don't understand the calculations money makes to report >these two measures. Can anyone enlighten me on what >exactly Money is doing to get ROI and Annual %Return? See http://support.microsoft.com/default.aspx?scid=kb;en-us;131664 Thank you. That is very helpful. Do you have a reference for the "Ann. Ret." which is shown as a % on the P...

Here's the problem, I like the idea about being able to freeze the the panes of a worksheet. However I would like to freeze certain cells, for example I want to just freeze I1 through I10. The freeze panes option grabs A1 through I10 which is not what I want. Any ideas on how I could get around this? Scott Scott, You can't freeze only part of a row or column. Freeze works only on entire rows and/or columns. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Illusvfyre" <sboisvert@cox.net> wrote in message news:C...

I would like to know if there is a way to reset a field dropdown list in a pivot table. In some field sometimes I delete data from the source and some items of the field will no longer be there. However, when I refresh the pivot table the dropdown list will still show the old items even if they're not in the source data anymore. I really need your help with this. Thanks a lot in advance for your help, Francisco Molina ...

Longines Evidenza Chronograph Mens Watch L2.643.8.73.2 Discount Watches Site : http://www.watchespurchase.com/ Longines Evidenza Chronograph Mens Watch L2.643.8.73.2 Link : http://www.watchespurchase.com/Longines-3997.html Longines Evidenza Chronograph Mens Watch L2.643.8.73.2 Information : Brand : Longines Watches ( http://www.watchespurchase.com/Longines.html ) Gender : Mens Code : L2.643.8.73.2 Item Variations : L26438732 Movement : Automatic Bezel : Fixed Case Material : 18k rose Gold Case Diameter : Dial Color : Silver...

I have a very simple piece of code that initializes a 2D int array: unsigned int** arr_int = new unsigned int*[sp1 + 1]; for(int i = 0; i < sp1; ++i) { arr_int[i] = new unsigned int[sp2 + 1]; for(int j = 0; j < sp2; ++j) { arr_int[i][j] = -1; } } The values of sp1 = 35000 and sp2 = 35000. I am getting a CMemoryException thrown at different values of i ranging from 1420 to 14030. Why is this happening? Is it not possible to allocate a 35000 x 35000 int array. I have a machine with 1GB RAM and decent processing power. Lots of space on disk and no virtual mem problems. T...

Using Excel 2002. I have a simple list of numeric data and I want to have a button for each item which will simply increase the value of the item by 1. I can't seem to do this without getting a circular reference. Try Eg: You have a number in A1 and try the following macro. What the macro is doing adding 1 to A1 (in D1) then copy- paste valu in D1 itself then copying the value of D1 to A1. Sub Macro1() Range("D1").Select ActiveCell.FormulaR1C1 = "=RC[-3]+1" Range("D1").Select Selection.Copy Range("D1").Select Selection.PasteSpecial Paste:=xl...

1) What is the Max stable size for an Access 2000 Database? My 750 MB A97 Database Converts to an 829 MB A2K database ...at what size will it become unreliable? 2) I say above that it converts but I'm not too sure... every time I attempt to convert it I get an "Out of System Resources" or "Out of Disk Space" message, which is odd as i have run it on machines with 256 and 512 MB RAM and at least 8 GB of free hard drive space. every time the file is the same size and appears to be complete.. I've been using the files for testing and they seem to work fine... but...

I have a report with a single column. I have a sub-report in the detail section of this report. The sub-report has two columns. Only one column prints in the sub-report. TIA for your help. Never mind....I got it. Gettin' old ain't for sissies! "R Fourt" wrote: > I have a report with a single column. > I have a sub-report in the detail section of this report. The sub-report has > two columns. > Only one column prints in the sub-report. > TIA for your help. ...

First off I cannot use the default filters because the report is being generated for a dashboard and not a CRM Report, however it is being created via an SRS Report. I simply need a way to filter the report for the current user only, I found this query which gives the system user but this will not match up to the Account owner because of its format. select fullname from FilteredSystemUser where domainname = SUSER_SNAME() Gives: CRM Admin I need, Admin, CRM ...

We run Exchange 2003 on a Windows 2003 server. As far as I am aware the MTA Stack service on 2003 is not required. This is just a throwback to 5.5. We do not have the Exchange MTA Stacks service running because of this but I keep getting event id 2000's. Event Type: Warning Event Source: MSExchangeIS Mailbox Store Event Category: MTA Connections Event ID: 2000 Date: 28/10/2005 Time: 10:16:29 User: N/A Computer: BCMAIL01 Description: Verify that the Microsoft Exchange MTA service has started. Consecutive ma-open calls are failing with error 3051. For more information, click http:/...

Sorry ... continuation of my previous e-mail. The hidden worksheet or separate workbook to be set up for example ... so username is in column A, date column B and time in column C. Fred Flintstone hu kiteb: > Sorry ... continuation of my previous e-mail. > > The hidden worksheet or separate workbook to be set up for example > ... so username is in column A, date column B and time in column C. What's to stop someone from copying the file and viewing it locally, or viewing it without making changes? It is quite easy for a given user to see the contents without forcing a sav...

Hi, I need a function that helps find the middle value. For example, if I have 12, 23 and 100, it must show 23. Or if I have 1,2,3 must show 2. Thanks Here's a generalised solution to the problem: =3DLARGE(A1:A10,ROUNDUP(COUNT(A1:A10)/2,0)) Your numbers can be anywhere in the range A1:A10 (as an example), and works best if you have an odd number of numbers. Hope this helps. Pete On Dec 18, 1:31=A0pm, Diana <dianamrco...@gmail.com> wrote: > Hi, > > I need a function that helps find the middle value. For example, if I > have 12, 23 and 100, it m...

Hello! I'm new to the group and I'm working on an excel sheet. What I'm trying to do is, in a column have a number ex. 36 repeat that number 72 times and on the 73 row increase the number 36 by 1 (to 37). I need to do it about 730 times. I don't know where to start. I've tried to do an auto fill but it's increments by a decimal. The sheet should look like this. Column A 36 36 36 36 36 (72 times) 37 37 37 (72 times) Can anyone help? Thanks, Darrell --- Message posted from http://www.ExcelForum.com/ One way, put in row1 =FLOOR(36+(MOD(ROW(1:1)/73,72)),1)...

thanks Herbert. Herbert Seidenberg Wrote: > Copy Date to a column with Advanced Filterunique records only. > > =SUMPRODUCT((Date=Date2 R)*(Name=Name2 C)*Data) Is it like? Sheet1 = original (1D) list Sheet2 = one with AdvFilter-unique Sheet3 = resultant 2D table. I suppose R and C are intrinsically defined as Row and Column? still trying.... Do you think I can extend this to multi-dimension analysis? thanx Frankie LAU. -- frtklau ...

Hi, I am not able to perform actions like ReplyAll, Updating a task item.. on my outlook 2003, am getting the following error - The messaging interface has returned an unknown error if the problem persists restart outlook. Prior to this installtion i had outlook 2007 beta installed on my machine but I uninstalled it, actually during the uninstall process i had accidentally clicked the cancel button, later on i did the uninstallation from Control Panel. Then i tried installing outlook 2003 though the installation got executed smoothly but am not able to perform the above mentioned actions. Als...

Port 25 on the router wasn't open so users couldn't recieve email for the day. I opened the port and now users are asking if email that was sent to them earlier is going to show up. Will this happen or no? So far users are saying they haven't recieved any emails from earlier on in the morning, where did the emails go? Can I recover them? if the port was not closed for very long, they will eventually receive everything. Most email servers are configured to keep trying to send mail to port 25 on an email server for 2/3 days. The intervals at which it retries get progressive...

I have an Exchange 2003 server and all user using outlook 2003, each of the user will have more than one profile set in outlook, everytime user change profile, they have to login again. any method to save the exchange passowrd? (if the profile login and password is the same as their window login, not password needed for Exchange) Give the user proper permissions on the object that they are trying to open the mailbox from in AD U&C. Just curious; why do they need multiple mail profiles for mailboxes on the same Exchange server? -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configur...

Subtracting cells C2 thru I2 Example: If C2 had 3436, D2 had 3395, E2 had 3376, then I need the answer in cell J2 to be 60 EVEN IF THE CELLS F2 THRU I2 ARE BLANK. This formula works fine if I have numbers in ALL cells (C2:I2) "=MAX(C2:H2)-I2" but when there are blank cell it doesn't count right. Thanks in advance! Richard If I have understood your question correctly, this formula entered in J2 will do the trick. =MAX(OFFSET(C2,,,,COUNTA(C2:I2)-1))-OFFSET(C2,,COUNTA(C2:I2)-1) You can't have for instance a number in C2, D2 blank and a number in E2. The blanks are suppose...