Index | Match Formula (help)

I'm using this formula 
=INDEX(Sheet2!$A$1:$E$30,MATCH(A2,Sheet2!$A$1:$A$30,0),MATCH(B2,Sheet2!$A$1:$E$1,0)) to return a value from a table. 
I've copied this formula do a column of cells and getting the #N/A message 
in the cell. I understand why this is taking place, because the cells are 
null. Can I include in this fromula something to make this return an blank 
value if the cells dont have data entered yet.
Sheet1 has the null cells not the table. Cell A2 and B2. I'm entering data 
in these cells as needed but would like to have the index formula copied and 
ready to find when data is entered.
0
RayG (15)
11/15/2004 2:29:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
837 Views

Similar Articles

[PageSpeed] 14

H
=IF(COUNTA(A2:B2)=2,INDEX(Sheet2!$A$1:$E$30,MATCH(A2,Sheet2!$A$1:$A$30,0),MATCH(B2,Sheet2!$A$1:$E$1,0)),"") 

"RayG" wrote:

> I'm using this formula 
> =INDEX(Sheet2!$A$1:$E$30,MATCH(A2,Sheet2!$A$1:$A$30,0),MATCH(B2,Sheet2!$A$1:$E$1,0)) to return a value from a table. 
> I've copied this formula do a column of cells and getting the #N/A message 
> in the cell. I understand why this is taking place, because the cells are 
> null. Can I include in this fromula something to make this return an blank 
> value if the cells dont have data entered yet.
> Sheet1 has the null cells not the table. Cell A2 and B2. I'm entering data 
> in these cells as needed but would like to have the index formula copied and 
> ready to find when data is entered.
0
frank.kabel (11126)
11/15/2004 2:36:01 PM
Reply:

Similar Artilces:

Trouble matching overdue payments
This seems to be a constant problem that I would like to find a fix for. Money (2005 s.b.) somehow doesn't match payments from my credit card to reoccurring bills. It then shows that I have an overdue bill even though I paid it. Is there some way to match the overdue bill to a payment I already made? It seems like my only options are "skip occurrence" (which I always do) or "enter into register". Both options suck. Anybody have any ideas? Thanks. ...

To index or not to index
I have a table of stuff stored in a repository and an attached table of inventory dates, linked one-to-many by an Autonumber ID field. I regularly need to find the oldest or newest inventory dates (or all, in order by date) for each item record in the inventory table, which is normally an automatic case for indexing. But this stuff is not inventoried very often, so far, only two out of over 80,000 records have three records in the inventory table, all others have zero, one or two inventory records. This is NOT going to change. It will likely be decades before there are as many as...

Exchange 2000 running out of disk space help!
I have been going through user emails and backing up the large attachments to another drive I thought that would free up space but its not working. I can only free up disk space by moving the log files from the MDBDATA folder. Why cant I free up disk space it is slowly getting smaller and smaller right now its at 400mb and in a day or two the logs have taken up all the disk space and the server crashes please help! Have you tried to defrag your infomation store? "dath" wrote: > I have been going through user emails and backing up the large attachments to > another...

Relative Formulae between worksheets
I have a series of worksheets within a workbook and want to advance a date by 7 on each work sheet. The date is in Cell A1 in worksheet 1 and on worksheet 2 I have the formula ='Sheet1'!A1+7. When I copy and past this into worksheet 3 it does not change the formula to ='Sheet2'!A1+7 but retains the origianl sheet reference. Is there any way of achieving what I want or am I going to have to change the fromula on each worksheet manually? I am currently using Excell 2003. -- Skid References between worksheets are always absolute in Excel so you'll have to edit the for...

INDEX/MATCH help
I just learned about the INDEX/MATCH function while searching some of the Excel tip pages - and I think I can make good use of it. What I want to do is this: worksheet 1 - is the format of our Income Statement worksheet 2 (titled TB) is our Trial Balance. I want worksheet 1 to read TB (worksheet 2) and pick up the YTD amount for each account #. I tested this out and my formula is working fine. However, sometimes an account # on worksheet 1 doesn't appear on Worksheet 2 because there wasn't any activity. In this case I get a $N/A. I need to edit my formula so that if there ...

Help, please: to remove Outlook 2000 ?
Help, please... On a pretty good Win XP PC, I need to upgrade from Outlook Express 6 to Outlook 2000. I installed Outlook and it imported all the folders/messages/identities from OE6. Outlook 2000 starts up, and all seems fine, and I'm looking at the new message that it created... "Welcome to Outlook 2000". If I try to view any other message, it locks up. I see the perpetual hourglass. It's dead. I want to Remove it, then re-install it. How do I remove it? I went to Control Panel, Add or remove programs... it's not there. Add/remove Windows components... it'...

Excel Formula
What EXCEL formula can I use for the following: Cell E4 has Nov-09 (11/1/09 shows as Nov-09) currently. when I change this to Dec 09, I want the number $39,403.25 (from a different worksheet) to appear in E11. Currently E11 has $85,904.50 from the corresponding month on the separate worksheet (values listed below). The month dates below and data are from a separate worksheet. Jul-09 $0.00 Aug-09 $11,945.00 Sep-09 $11,043.01 Oct-09 $12,880.00 Nov-09 $85,904.50 Dec-09 $39,403.25 =VLOOKUP(E4,Sheet2!A1:B6,2) assuming your table in on S...

Copying Formulas
When I copy a formula from one spreadsheet to another (2 different files, not worksheets within the same file), the file name of the spreadsheet that I am copying FROM is copied to the cell of the spreadsheet that I am copying TO. I only want the formula itself, not the spreadsheet name. I tried PASTE SPECIAL but it didn't work. Thanks in advance for any help. In EXCEL 2007 when doing the Paste Special select the Formulas radio button then hit OK and you will get just the formula without the spreadsheet name. If my comments have helped please hit Yes. "Gingerly&q...

Linking...HELP (accidentally posted on Excel Programming)
I'm trying to change the source on a link. Every time I do it, however my system locks up. I actually have 4 workbooks that are linked up to one workbook. I hav to do the same thing three times, but the workbooks have to hav different names to them: ie: folder x -> worbookA WorkbookB workbookC workbookD BIGWORKBOOK new to use the exact information as above, but I have to have it in difference folder with difference names... folder y -> worbook1 Workbook2 workbook3 workbook4 SecondBIGWORKBOOK I've used the Edit-> Links-> change source (but that's where my syste loc...

address book---need help
I thought there was a ox u could check in outlook or outlook express such that when u reply to a message received, then that recipient's email automatically gets updated into your address book No such option in any current version of Outlook--only the old IMO mode had such an option. -- Russ Valentine [MVP-Outlook] "carmine" <anonymous@discussions.microsoft.com> wrote in message news:093a01c3a949$a91ca780$a001280a@phx.gbl... > I thought there was a ox u could check in outlook or > outlook express such that when u reply to a message > received, then that recip...

Formula in Macros
I am trying to do a average for a ongoing twelve week period in my macro. When the macro updates it adds the last column but I can't get it to drop the first column so it goes from averaging 12wks then 13wks,14 weeks and so on. Is there a command I'm missing. Thanks in advance for any help you can give me.I'm on Excel 2002. SH This code averages the 12 columns in the above row. ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1]C[-11]:R[-1]C)" By its nature, copy this formula across next week and it will pick up just 12 columns. What does your code look like? -- HTH ...

Help! How to create "fake" accounts in Money?
Here's my problem: I have a system of money management in which all income is split up and distributed into certain (fake) "accounts". (In reality, most of it is split up and distributed to an actual checking account and savings account.) Each of these fake accounts represent the balance that I have available to apply to certain aspects in life (e.g. bills/necessities, play, saving for spending, saving for investment, donations/tithing, education). So, for any actual deposit into my checking or savings account, I want to specify a certain amount OR percentage to go i...

Create Clustered index or Covering Index
Which would be better on a 3 column temporary table? A Clustered index or a Covering Index? In this case the uniqueidentifier is always the same and is done this way because there are about 20 procedures that already use this as a static table and I don't want to impace all the procedures with this change. CREATE TABLE #temp (a uniqueidentifier, PID int,CID int) CREATE CLUSTERED INDEX idx on #temp (CID) or CREATE TABLE #temp (a uniqueidentifier, PID int,CID int) CREATE INDEX idx on #temp (CID) INCLUDE (PID,a) Thanks, Tom Tom, Better for what? It depends....

Indexes in tables.
Hi, Can I add index for tables in SQL Manager. For example for SOP30200 index for any field. Will it be a problem with program or not? Any other ways? Thanks, Vitali I believe any future upgrade will blow away your index. -- Charles Allen, MVP "Vitali V" wrote: > Hi, > > Can I add index for tables in SQL Manager. For example for SOP30200 index > for any field. Will it be a problem with program or not? > Any other ways? > > Thanks, > > Vitali > > > Suggest you leave the tables alone. You'll just get into a pickle.. "Char...

Match & Index??
I have the following info in different workbooks. In workbook 1, I have in Columns A,B,C,D: Mark & No. Start End Deal SLGG1234 3/15/2004 6/15/2004 211 SLGG1234 1/1/2004 3/14/2004 111 SLGG1234 6/16/2004 8/15/2004 311 SLGG1255 2/13/2004 8/15/2004 411 In workbook 2, I have Columns A,B, C: Mark & No. Date Deal SLGG1234 3/14/2004 SLGG1234 6/14/2004 In Column C of workbook 2, I want a formula to look at Cols A and B. Compare the info in them to Cols A,B and C in workboo...

subtotal, match?
Morning all. I need to do a name check to match with a subtotal function. Is that possible? I was thinking that subtotal(Func_Num,match(....)) might handle it, but I don't see anything that'd allow for that in the list of function numbers for subtotal. My goal is to perform a subtotal type operation, to compare names in a field range from one worksheet to another. I'm already performing a subtotal operation on one field, and wanted to do an if test on the names that show up with each operation. With the false response, I'll then be changing the name eleme...

text and picture colour matching
Hi there I am producing a document which has a picture and text. I am trying to make them the same colour. I set the text and the picture to the same rgb colour numbers 255 (i.e. royal blue). They look the same on the screen and yet when they print out the picture and the text are very different shades of blue. It's an issue I've had before in pub 2003 but didn't in pub 2000 on the same printer! Any suggestiions greatfully received. Thanks Ken Are you running the most current printer driver? -- JoAnn Paules MVP Microsoft [Publisher] "Ken" <anonymous@discussio...

Locking Formulas
Hello, I need to average 3 cells. However, whenever I add a new column, the formulas shift by a cell, but I dont want them to. How do I keep the formulas from changing? Example: Avg Cell F Cell G Cell H Now, I want to Avg Cell F-H However, when I insert a new cell to put this latest years numbers in I want what is currently in Cell H (Soon to be Cell I when I insert a column at Cell F) to drop out of the equation. I still want to Avg Cell F-H but Excel keeps shifting the formula to Cell G-I. Ok, hope that makes sense. How do I lock the formula? -- Thadar -------------------...

Formula that will represent years & months worked
I am trying to figure out a formula that will calculate how many years & months an employee has worked for the company. I found a formula that will give me the years, but not years & months. Take a look at Chip Pearson's notes at: http://www.cpearson.com/excel/datedif.htm Pam wrote: > > I am trying to figure out a formula that will calculate how many years & > months an employee has worked for the company. I found a formula that will > give me the years, but not years & months. -- Dave Peterson =today()-hiredate Then format that cell as Special YY:MM...

Why do my data labels do not match source data?
I have created a chart but my data labels do not match the source data. The labels state, Series 1, Series 2, etc. Hi, If the labels read series 1, series 2 etc it would suggest that you have not specified a range in which to pick up the series name from. Right click the chart and pick Source Data... On the Series tab check the Name control contents. Cheers Andy KeithRD48 wrote: > I have created a chart but my data labels do not match the source data. The > labels state, Series 1, Series 2, etc. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

MVP's, please help me understand SUMPRODUCT.
Please refer to Ntobias' thread on Aug 24, 2006 and Sandy's comment before you read on. Sandy, Thank you for your comment and a different perspective. Before JE's post, I already had a hard time understanding SUMPRODUCT. When I read JE's post I did more research. Excel help was not a big help and I got lost in cyber space encountering things like matrix Algebra. To make a long story short - I am getting more confused with SUMPRODUCT and not sure if I understand it. Can someone point me to some good and straightforward tutorial please? I found the following examples from ...

status validation help
Hello, I need some help coding this one... I have 3 fields in a form Amount, Date, and Status. If the user inputs an Amount then they must enter a "Date" in order for the database to accept a Complete "Status". The Status field has several drop down choices, Open, Pending, Complete, etc. Any help would be greatly appriciated!! Thanks, ~Gabe On Thu, 3 Jun 2010 16:22:09 -0700, Gabe <Gabe@discussions.microsoft.com> wrote: >Hello, I need some help coding this one... > >I have 3 fields in a form Amount, Date, and Status. If the user inpu...

Formula to count specific items in a certain date range.
I need to count items in a spreadsheet that fall into specific categories & date ranges. The spreadsheet is for the entire year and contains about 10 categories. I can get it to count the items (I used COUNTIF(I2:I200,"=Incomplete"), but I can't get it to exclude certain date ranges. Example, for the month of May I need a count of items titled "Incomplete" I have searched many different websites but I can't seem to locate the correct formula - I am probably just overlooking something simple! Any help would be appreciated! >The spreadsheet is for...

Under HELP, where is the INDEX
In Word 2003, is the INDEX completely gone? I always used it to search topics and definitions of terms. Hi MS decided to change the help system :-( So the Index feature is gone. Maybe you have your old Office CDs still available and can copy the old help files -- Regards Frank Kabel Frankfurt, Germany "Perotin" <Perotin@discussions.microsoft.com> schrieb im Newsbeitrag news:34B1A278-5835-48DD-9A74-1E0E78441485@microsoft.com... > In Word 2003, is the INDEX completely gone? I always used it to search topics > and definitions of terms. ...

index
Hi guys, I have a web site I developed in Publisher Windows 98. We now have a new version. When I work on it and then publish to the web, it does not create a index page, infact it does not see page 1 ! I inserted a blank page as new number 1 and then it made page 2 as the index page ! Appears to work but what am I missing. Also where I have photos and a frame around them, it makes two files/copies of each photo to be sent to web site. Like 505 and 5051. In 5051 the photo is of pour quality but 505 is fine. Have not sent over yet as it appears I have to remove the existing index...