Complicated use of INDEX and MATCH to lookup multiple columns

Hi, I am trying to do a version of the formula given for method 1 here:
http://support.microsoft.com/kb/214142

Using Excel 2007, I have to retrieve a value in column D of sheet Media by 
matching the value in sheet Media column A to sheet Promo column A and also 
checking that the dates in sheet Media columns B and C overlap with the dates 
in sheet Promo columns D and E.

I get an #N/A error for a value/values that should be found when using the 
following formula:

{=INDEX(Media!$D$2:$D$4473,MATCH(Promo!A2,IF(OR(AND(Media!$C$2:$C$4473>=Promo!D2,Media!$C$2:$C$4473<=Promo!E2),AND(Media!$B$2:$B$4473>=Promo!D2,Media!$B$2:$B$4473<=Promo!E2),AND(Media!$B$2:$B$4473<Promo!D2,Media!$C$2:$C$4473>Promo!$E$2:$E$6670)),Media!$A$2:$A$4473),0))}

If anyone could explain why this is not working I would be hugely grateful.
Thanks for your time
Craig

0
cHaynes (2)
10/6/2008 2:48:01 PM
excel 39879 articles. 2 followers. Follow

1 Replies
321 Views

Similar Articles

[PageSpeed] 55

Sorry to double post but after some more experimentation I've found that a 
simplified version of the date comparison works. Any time I use 'AND' 
however, it breaks again. This is the version with just an OR...

{=INDEX(Media!$D$2:$D$4473,MATCH(Promo!A2,IF(OR(Media!$B$2:$B$4473>=Promo!D2,Media!$C$2:$C$4473<=Promo!E2),Media!$A$2:$A$4473),0))}

"cHaynes" wrote:

> Hi, I am trying to do a version of the formula given for method 1 here:
> http://support.microsoft.com/kb/214142
> 
> Using Excel 2007, I have to retrieve a value in column D of sheet Media by 
> matching the value in sheet Media column A to sheet Promo column A and also 
> checking that the dates in sheet Media columns B and C overlap with the dates 
> in sheet Promo columns D and E.
> 
> I get an #N/A error for a value/values that should be found when using the 
> following formula:
> 
> {=INDEX(Media!$D$2:$D$4473,MATCH(Promo!A2,IF(OR(AND(Media!$C$2:$C$4473>=Promo!D2,Media!$C$2:$C$4473<=Promo!E2),AND(Media!$B$2:$B$4473>=Promo!D2,Media!$B$2:$B$4473<=Promo!E2),AND(Media!$B$2:$B$4473<Promo!D2,Media!$C$2:$C$4473>Promo!$E$2:$E$6670)),Media!$A$2:$A$4473),0))}
> 
> If anyone could explain why this is not working I would be hugely grateful.
> Thanks for your time
> Craig
> 
0
cHaynes (2)
10/6/2008 3:26:16 PM
Reply:

Similar Artilces:

Another multiple criteria/column question
Ok, first post and pretty much a new user to Excel. I have two sheets that I am working with, trying to recall data from one to the other that meets criteria. In a nutshell: Sheet 1 contains a column of unique values (col A), cols C,D, and E are where I want to insert the formula to find data on Sheet 2. Sheet 2 contains 4 columns, A contains multiple occurrences of each value (from Sheet1, column A), each with its own timestamp in column D. On Sheet1, in column C, I want to find a value on Sheet2 in column A and return the timestamp in column D. I know I can use VLOOKUP for this. =VLO...

Controlling printed records when report bound to multiple tables
I created a report that uses the control from a form to generate a report based on that record's primary key. This form also has a subform which has relationships tied to the primary key for record identification and is linked to the main table. When preview the report the data from the subform either does not show up in the preview when using the filter [control]=[form]![control].[value] or makes multiple copies of the report equal to the number of entries in the subform's table. Is there any way around this? I have tried queries but have not found a way to use a f...

Move to match cells
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 000801097-3 1998 11 000801091-7 009601098-9 2004 000801098-...

setting up multiple email accounts #2
I followed the rules for setting up the pop3 and smtp accounts for multiple mail accounts, but when I access outlook express the additiona account does not recognize the password I've asked it to remember. HELP! KCB <anonymous@discussions.microsoft.com> wrote: > I followed the rules for setting up the pop3 and smtp > accounts for multiple mail accounts, but when I access > outlook express the additiona account does not recognize > the password I've asked it to remember. HELP! Ask in an Outlook Express newsgroup. -- Brian Tillman ...

How to shift address listings from row list to columns?
I have a mailing list with name, address, city, state & zip with each item in individual rows like a list of labels and a few empty rows of space between each listing. How do I create/transfer this list into columns accross so I can sort by city or zip? Thank you! If your data is nicely grouped, with each group in 5 lines, viz: name add city state zip then an earlier suggestion given which worked might be worth a try: See: http://tinyurl.com/wgcb -- hth Max ----------------------------------------- Please reply in newsgroup Use xdemechanik <at>yahoo<dot>com for email --...

Sorting with Column has Formula
Hi everyone, I never imagined that the formula in the column would affect the sorting order in any way, but it does in my case. Below is the formula in that I have in Col I, and I'd like to sort it in Ascending order, but the result is that it sorts with all the empty rows on top and the one with the result from the formula at the bottom. I assume it consider the "I" in the "IF" function in the formula, but I'm not sure. Can anyone tell me how to fix this please? =IF(E2="","",IF(J2="X","Priority #1",IF(...

Permissions is not allowing me to use my print preview.
Help! Don't understand My Permission in Excel. Even though I unrestrict, it still doesn't allow me to Preview my documents. How do I reset? Please Help, ...

Excluding hidden columns and rows when copying to another workbook
When I print part of a worksheet that has hidden columns and rows - the hidden columns and rows do not print. That's what I want. Now--I'd like to take that same data and copy it to another workbook excluding the formulas and hidden columns and rows so that the new file contains only the data as was printed. How can I do that? PJ Select your range including hidden rows and columns then Edit>Go To>Special>Visible cells only>OK Now do your copy/paste. Gord Dibben Excel MVP On Fri, 6 Feb 2004 10:16:07 -0800, "PJ" <anonymous@discussions.microsoft.com> wr...

How to get TASK_ID field for summary tasks without using Project.a
I know for tasks which are not summary tasks we can get TASK_ID field using statusing web service. But i could nto find any other options than Project web service to get TASK_ID field for summary tasks and the top level project task. Problem of using Project web service is that in my custom sharedpoint web part where we are using PSI web services we get all the data required using Resource and Statusing web service for the logged in resource. But Statusing web service retrieves TASK_ID only for actual tasks and not for summary tasks. Now just to get TASK_ID of summary tas...

Question on multiple NICs used by Exchange 2003
I have a requirement to bind four virtual SMTP servers to unique IP addresses. The server has four NICs and each IP address is uniquely bound to a virtual SMTP server. I used telnet to confirm that the appropriate virtual SMTP server responds to each assigned IP address. In case you are wondering why I did not use virtual IP addresses- Per Microsoft, the virtual IP address schema will not satisfy our requirement that the same IP address be used for sending (relaying) the e-mail that received it. Their recommendation was to have a NIC for each virtual SMTP server. The problem that I am ex...

2007
Project desktop expert, new to Project Server 2007 and working with a sandbox implementation currently. When I use the PWA Build Team...Replace to replace a generic resource on a project with an actual named resource after first publishing a plan, my updates don't appear to be reflected in Project Server. If I check out and open the Project Plan in MSP Pro, however, the updates were made, and then if I publish the schedule, the updates appear in server. I thought when you made resource updates using PWA that they should be reflected immediately (or as soon as the request...

Varying column widths
I have 2 excel sheets - one is 20 columns wide the 2nd is 7 columns wide. The 20 column sheet has a general information section at the top that I would like to add to the 7 coulmn spreadsheet. However, when I copy and paste it "spreads out" or goes way beyond the width due to the number of columns. I have tried paste special object and entering the excel sheet that way and it doesn't fit properly and too many cells are shown. Any other way to do this? If you only want it up there for appearance purposes, you can try: Copy the selection. Click the cell where you want t...

retain only rows with condition that cells in column H containing "AU"
I have a large database containing columns with one column specifies the product name. In one instances, I need to retain only rows with condition that cells in column H containing "AU" of the entire string in the cell. Delete those rows without it. The problem is , that the AU of the string can appear in any position, not a fixed position. Is there any simple way of doing it? Many Thanks! Regards, Bora Hi, You can use AutoFilter. Choose the command Data, Filter, AutoFilter. Then open the autofilter drop down in the column H and choose Custom and from the first drop down, t...

Developing a robust database at one co. then using it at another c
Are there any legal implications of using the type of database listed above to perform similar functions for another company? The 2 company's are in 2 totally different industries and the new database will have to be modified to fit the requirements for the industry it is in. The database was developed at the old company. there is no such thing as a robust Jet database. Move to SQL Server if you want to build a solution that will work for the next decade. Jet is and always has been depecrated On Apr 7, 12:14=A0pm, BoaMan10 <BoaMa...@discussions.microsoft.com> w...

Equivalent Column Break
In MSWord you can insert a column break when doing newspaper columns. If I have two text frames connected, and I am almost at the bottom of one but want Publisher to start at the top of the next one, how do I insert a 'break'? or do I have to press return until it move the text. Thanks Anita (1) You could shorten that column so that your text breaks where you want it to. (2) Or you could check the Help file and search for "insert break". Insert a column break You can insert a column break anywhere in a text box. If the text box contains more than one column, the ...

stationary, templete, signatures with multiple accounts
Hello all, I use Outlook 2002 to access three email accounts ( Work, Hotmail, and home ISP ) Obviuosly I can choose from which account I'm sending from at the time of mesasge creation. What I am asking is, is it possible for when, let's say, I choose to send from my work account, a certain stationary and/or template and/or signature is used ----- and a different stationary and/or template and/or signature is used when I send from my ISP, etc....... Please advise, preferably be sending directly to patrick072040@comcast.net., or by replying to this post. Thank you in advance !! O...

How do I create a summary page from multiple worksheets
Trying to roll-up information from multiple worksheets within the same workbook to a summary page. These worksheets are copies of each other. For example: each worksheet has a column labeled "defect number". The users can record multiple defect numbers within a cell (e.g. 897, 992, 1001) So sheet1, row1 = 897, 990 sheet2, row1 = 992 sheet3, row1 = 995, 1001, 1012 sheet4, row1 = empty How do I (or can I) rollup this information to a summary page where sheet5 is the summary worksheet and row1 = 897, 990, 992, 995, 1001, 1012. Here's what I have so far [=Sta...

Using the classes created with xsd.exe
I have created classes from several xsd files. These files create about 150 classes and spot checking them they do represent types in the xsd files. the question is how do I use these files. How do I load data into them and create xml from them. Is there some articles about this subject. Thank you, -- Jerry Hi Jerry, As for the classes you've generated, are they normal classes or dataset classes? As for the normal classes you generated through xsd.exe, you can use XML serialization to convert those class instances into XML content or deserialize the XML content back into objec...

Hiding Column and Row Bars.
I know theres a way to costumize the way you view an excel page by hiding toolbars, but is there a way to hide the rows and column bars just so all you can see is the actual page. E.G. is there a way to Hide the top bar that defines the colums "A", "B", "C","D" and Rows 1,2,3,4,5. Go to Tools>Options>View, there are a number of options you can play with there. -- HTH RP (remove nothere from the email address if mailing direct) "tamato43" <tamato43@discussions.microsoft.com> wrote in message news:7D1E5DEB-D6F2-47C8-95E2-2...

Column comparison
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 Hi Dave, Need more information like a sample of the existing data plus a sample of what you want left. Maybe an explanation of the criteria for what needs to be deleted. -- Regards, OssieMac "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 than...

Disallowing duplication of nmbers in a column
Is Excel capable of disallowing the same numbers in a column? I have a column in a sheet that invoice numbers are entered into. I would like that column to alert or something if duplicate numbers are typed in. Hi Have a look here: http://www.cpearson.com/excel/NoDupEntry.htm -- Andy. "Barb1" <Barb1@discussions.microsoft.com> wrote in message news:E3631DD3-724E-4C0D-956B-4201876A9A4A@microsoft.com... > Is Excel capable of disallowing the same numbers in a column? I have a > column in a sheet that invoice numbers are entered into. I would like > that > c...

How to use AjaxToolKit in asp.net for DHTML Editor
Dear Sir/Madam Please tell me how to use AjaxToolKit dll for DHTML purpose using C#. Thanks in Advance "Deep" <vinodkus@gmail.com> wrote in message news:7ac633e9-2cbd-4417-9b4e-363a9b5b3e74@d27g2000yqf.googlegroups.com... > Please tell me how to use AjaxToolKit dll for DHTML purpose using C#. http://tinyurl.com/ybfuqo8 ...

Lookup Help
I have a spreadsheet with three tabs. Tab 1 needs to perform summary calculations Tab 2 & 3 are ODBC connections with source data from other files. Tab 2 is a sql query that is a cross tab with columns 1-4 representing row data and then the remaining columns are employee ids with a productivity value in their column. Not all columns have the same value as they correspond to a job/service performed on a specific date. My problem Tab 1, column 2 contains "completion dates from 1/1-current and refreshes based on tab 2 each time it opens. I need a lookup formula that wi...

multiple balances on one printout
I want to print all the months on one sheet. with a total, is this possible and if so how do I do so. Thanks ...

How to merge columns and rows into one cell besides using Merge and Center Icon?
I had posted this question before, but I couldn�t find this thread in any of the pages up till page 17. So I�m posting again. I want to merge all rows and columns starting from A1 to J2, with no lines in between into one cell. My text data value are in D1 and D2 respectively. Using Merge and Center Icon will only retain the upper-left most data, resulting the data in D2 to be deleted. So how to merge all rows and columns into one cell and yet prevent the data in D2 to be deleted? Any help will be greatly appreciated. ------------------------------------------------ ~~ Message posted from h...