#### Index Match Problem

```I'm using the following formula, but it's returning #N/A:

=INDEX('File X'!\$F\$1:\$F\$2000,MATCH(1,(\$B5='File X'!\$A\$1:\$A\$2000)*(S\$4='File
X'!\$G\$1:\$G\$2000),0)

However, if I replace the S\$4 with "7710", it returns the correct value.
The number in S4 is 7710.  Why won't the formula recognize the cell
reference, but if I type the contents of the cell manually, it works?  I
double-checked to make sure nothing was entered as text.
```
 0
Utf
4/28/2010 5:06:01 PM
excel.worksheet.functions 4936 articles. 2 followers.

3 Replies
785 Views

Similar Articles

[PageSpeed] 1

```Hi,
is 7710 a number typed or imported, check if you don't have blank spaces in
one of the two cells you are comparing

"jeikenberry" wrote:

> I'm using the following formula, but it's returning #N/A:
>
> =INDEX('File X'!\$F\$1:\$F\$2000,MATCH(1,(\$B5='File X'!\$A\$1:\$A\$2000)*(S\$4='File
> X'!\$G\$1:\$G\$2000),0)
>
> However, if I replace the S\$4 with "7710", it returns the correct value.
> The number in S4 is 7710.  Why won't the formula recognize the cell
> reference, but if I type the contents of the cell manually, it works?  I
> double-checked to make sure nothing was entered as text.
```
 0
Utf
4/28/2010 5:12:09 PM
```I typed it in myself.  I did double-check to make sure there are no spaces.

"Eduardo" wrote:

> Hi,
> is 7710 a number typed or imported, check if you don't have blank spaces in
> one of the two cells you are comparing
>
> "jeikenberry" wrote:
>
> > I'm using the following formula, but it's returning #N/A:
> >
> > =INDEX('File X'!\$F\$1:\$F\$2000,MATCH(1,(\$B5='File X'!\$A\$1:\$A\$2000)*(S\$4='File
> > X'!\$G\$1:\$G\$2000),0)
> >
> > However, if I replace the S\$4 with "7710", it returns the correct value.
> > The number in S4 is 7710.  Why won't the formula recognize the cell
> > reference, but if I type the contents of the cell manually, it works?  I
> > double-checked to make sure nothing was entered as text.
```
 0
Utf
4/28/2010 5:36:01 PM
```Check whether S4 cell is formatted as text . If so either change that to
general format and re-enter the number or modify the formula as

(--S\$4='File X'!\$G\$1:\$G\$2000)

--
Jacob (MVP - Excel)

"jeikenberry" wrote:

> I typed it in myself.  I did double-check to make sure there are no spaces.
>
> "Eduardo" wrote:
>
> > Hi,
> > is 7710 a number typed or imported, check if you don't have blank spaces in
> > one of the two cells you are comparing
> >
> > "jeikenberry" wrote:
> >
> > > I'm using the following formula, but it's returning #N/A:
> > >
> > > =INDEX('File X'!\$F\$1:\$F\$2000,MATCH(1,(\$B5='File X'!\$A\$1:\$A\$2000)*(S\$4='File
> > > X'!\$G\$1:\$G\$2000),0)
> > >
> > > However, if I replace the S\$4 with "7710", it returns the correct value.
> > > The number in S4 is 7710.  Why won't the formula recognize the cell
> > > reference, but if I type the contents of the cell manually, it works?  I
> > > double-checked to make sure nothing was entered as text.
```
 0
Utf
4/29/2010 5:25:02 AM

Similar Artilces:

Hi all. I�m trying to load icon from file using LoadImage and add that icon to system tray. However, seems like the LoadImage doesn�t work properly because Shell_NotifyIcon doesn�t work with that icon. I tried with application icon (AfxGetApp()->LoadIcon(IDR_MAINFRAME) and everything works normally. I used the following code to load image: return (HICON)::LoadImage(NULL, lpszFileName, IMAGE_ICON, 16, 16, LR_LOADFROMFILE | LR_DEFAULTCOLOR); I tested that code in test application and icon is displayed properly. Icon on the disk has 32bit colors and size is 16x16, if it matters at all....

Publisher 2007 duplexing problem
Hello, I'm having a problem trying to get Publisher 2007 to print anything double-sided in my Toshiba eStudio 210C printer (networked, not connected directly). Whenever I try, I get a duplexing wizard that makes me manually turn over the paper. In Publisher 2003, I was able to print on both sides without an issue but when I upgraded I encountered this problem. Could someone help? --Nick Nick, Take a look at the Toshiba Support website for an updated printer driver. -- Don Vancouver, USA "Nick Schwartz" <schwartz@ypsilibrary.org> wrote in message news:uARN79...

Problem Installing BP SDK
When I try to install BP SDK I get "Visual Studio.NET has not been detected" I have installed VS2005. Does the SDK not recognize this? Is there a workaround? ...

Sophos 'Mailmonitor for Exchange'
I'm trying to find out if any other suppliers/users of Sophos anti-virus are having problems with Mailmonitor for Exchange. Before getting into the detail, let me just say that Sophos were first advised of this particular problem on 25th November 2003 (case id AVK-25-FGT for those that are interested). They have previously been made aware of everything in this posting. As a long time advocate of Sophos and MD of a 'Sophos Certified Partner', their help in resolving this particular issue has left me completely under-whelmed and we are now evaluating alternative products for the c...

Macro Problems
Hello everyone. I am using Excel 2000, and I am having a macro problem. On sheet 1 I have a dropdown that I use data validation with and a macro. This list sorts alphabetically, and I can type new names in when I need to. It works fine, but I need to have two to three more dropdowns on the same sheet. I tried to modify the macro, but it will not work properly. I created a new list "VendorList" and modified the macro.(I think that is where I messed up) here is a copy of the modified macro: Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Dim ws As Workshee...

can't create rules in outlook
When I try to create rules in outlook, I get a message which says the rule can't be created and to check my folder permissions. If I go through the wizard, I can create them successfully. It is really frustrating, because most of what I want to create would be simple to create with the create button. I'm the only user on this machine. It is running Windows XP pro and I'm the admin. I'm using Outlook 2002. Anyone have any ideas? Thanks, Anne ...

problem syncing OAB
microsoft.public.exchange.admin Hi, Outlook 2003 in cached mode connecting to Exchange 2000 SP3. Recently rebuilt a server and moved all the public folders to another server. Around that time started getting these errors when opening Outlook. Tried deleting and recreating the ost file already. 9:27:33 Downloading from server 'pmci-10.myserver.mydomain.net' 9:27:34 Error synchronizing folder 9:27:34 [80040119-501-0-5C0] 9:27:34 The client operation failed. 9:27:34 Microsoft Exchange Server Information Store 9:27:34 For more information on this failure, click the URL belo...

running total #N/A problem
hi, i am trying to make a stock register. I have the following row fields: Product, transaction_date and following data fields: receipt, issue, balance (balance is a calculated field whose formula is receipt - issue and shown as running total with base of transaction_date) it is running perfectly allright till i add one more row field called transaction_no after transaction_date. the running total column gives me a #N/A. I am trying to make a stock register that shows the product, dates and transaction no's of each product and five a running total at the end of every transaction. i...

problem with a conditional max problem
-- What formula could you enter in cell E13 so that if a value is entered in cell E12, cell E13 will display the value of f(x) that corresponds to the largest tabulated x which is less than or equal to the value entered. For example, if 3 is entered in cell E12, then E13 should display the value of f(0). Brian If your tabulated x's are in a range named "tabx", one way: =f(LOOKUP(E12,tabx)) where f() is your function. In article <8D7078DA-197C-4F2B-B7D0-129DEDC8EC8D@microsoft.com>, "Brian Cornejo" <BrianCornejo@discussions.microsoft.com> wrot...

Problems with merged cells
I have merged a fair few cells so that I can enter paragraphs, once I type past a certain point it wont bring up everything I have entered in the cell, it cuts half of it off & it wont print it either, but when i click on the cell all the information is there......can anyone help me out with this??? Excel has limitations (see link below). Cells will show up to 1024 characters, but up to 32,767 will appear in the formula bar. Printing.. maybe it has a 1024 character limit too. (Just because you're using merged cells doesn't mean you get more characters - they're ...

MFC Extension DLL problem
Hi, I created a DLL to make use of a preexisting MFC application. I want to use some dialogs, views and some other stuff from it. Except for some linker warning I got the dll compiled. If I now start using a dialog from the DLL I get an Debug Assertion Error, when exiting my client application. The Expression is: _CrtIsValidHeapPointer(pUserData) and the error was triggered in dbgheap.c . So, I assume I have a memory leak somewhere. Do anyone have an explanation for this? After researching a bit, it occured to me that I have two heaps, one for the DLL and one for my client application. I th...

Problem with XP
HI ALL, Last time, i have to face a very strange runtime error. I have developed an application using VC6 and windows 2000 server. When I run this application on XP, and when drag a DIALOG and is moved off the right corner of application, a runtime error occur and application closes Regards, Ghazanfar Ali Ghazanfar Ali wrote: > HI ALL, > Last time, i have to face a very strange runtime error. I have developed > an application using VC6 and windows 2000 server. When I run this > application on XP, and when drag a DIALOG and is moved off the right corner > of applic...

==== What I want to do: ==== I am hosting a "Microsoft Web Browser" control in my MFC program. I want to draw a red rectangle encompassing one or a group of html elements. Since the rectangle may encompass more than one elements, it seems to me that it is impossible to do it by changing element styles using IHTMLStyle interface. ==== The Method I am using now ==== Currently I am using "rendering behavior" method described in this topic: http://groups.google.com/group/microsoft.public.inetsdk.programming.d... which offers some sample codes as follows: &...

Inter-store transfer problems. Becoming a big problem. #2
We have HQ and Store Ops here in the office. Our warehouse manager uses Store Ops to transfer out inventory to the various store locations. We've been experiencing some unusual problems. For example, a transfer out request was made here using Store Ops last week but I don't think it went through, yet the HQ transfer reports states that order number is closed. The store we transferred to, their store ops does not even list the that order number in their "transfer in" list even if I filter the status to all and set the dates accordingly. Meanwhile the store ops in the offic...

Having great problem with an C# application
Hello! We have a C#.ASP.NET application that runs on a IIS 6. The application contains actually several asp pages but there is no GUI. The application receive an xml file that is processed. There is also an MFC dll that is called from this asp application to make a syntax check on quite many commands. You don't have to know what a command is. The problem that we get is the following when the asp pages calls the MFC dll it will crash in such a way that the whole application pool on the IIS will be restarted. The IIS runs on a windows server 2003. We have added some rows in the MFC dll to ...

"No indexers" on creating "New Shared Services Provider "
Hi to all, I've this problem, I what to create the new and first "Shared Services Provider", but on "Index Server" section I don't find any Index Server (in combo-box). What can I do? Where am I wrong? Bye Dario Concilio Make sure you configured and started the Search services. -- Daniel A. Galant Imagine what we could be... if we could just imagine. "Dario Concilio MCP" <dario.concilio@hotmail.it> wrote in message news:uhcKStDzKHA.5040@TK2MSFTNGP02.phx.gbl... > Hi to all, > I've this problem, I what to creat...

Charting Problems #2
I need to create a chart that has axes in "exact" inches. For example, the printed plot area is 6" x 3", or at least 1 axis is exactly inches. ...

Index / Contents Page
Is it possible to insert an index /contents page in Visio? I have a 30 page Visio document and on Page 2 I want to have a contents page so that the reader can reference a page quickly. Is it possible with out having to manually type it i.e. If the page order changes your index / contents is automatically updated as with Word. There should be an example of create a TOC using VBA at www.mvps.org/visio/VBA.htm John... Visio MVP Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm Need VBA examples? http://www.mvps.org/visio/VBA.htm Common Visio Questions http://www.mvps.org/visi...

2007 charting problems
Is anybody having these problems, and can anybody tell me how to fix them? 1. In Excel 2003, I could select a range of cells and drag them into a chart and they would be pasted as a new series into the chart. Is this feature gone in 2007? 2. When I have a rotated Y-axis label, I can click on the text and see an insert cursor, but when I start typing, only the first letter appears in the label and the rest end up in the formula bar. When I hit enter, only the new text from the formula bar shows up in the label. 1) Just Copy the range, right click the chart and use Paste 2) Do not use ...

New Win7 computer problems
Also posted in Lsoft group I bot a new HP p6240f computer with win 7. Loaded excel 95, then xl97 then xl2003. Then tried it. There must be something wrong in Windows or the video card cuz I get narrow columns and wide rows and misshapen shapes and small fonts in the formula bar. I can send a screen shot to anyone who may be able to assist. Normally, when I migrate from one computer to another all I have to do is adjust the zoom. All programs run fine on my Vista computers. I have tried the new LG2486L monitor on my notebook and it worked just fine and I tried an old dell 17"...

How many cells match a text string?
I have a sheet where I need to display a sum in one cell of a row that indicates how many other cells in the row contain text matching a given string. FIND or SEARCH can be used to tell whether any given cell contains a match, but is there a way, short of programming, to determine how many cells contain a match? Thanks, George Marshall George Try: =COUNTIF(A2:L2,"*text*") Andy "George Marshall" <george@NOSPAMmarshalls.org> wrote in message news:MPG.1a2f54291417b31e9896b9@msnews.microsoft.com... > I have a sheet where I need to display a sum in one cell ...