#### 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.

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

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...

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 ...