Error in ISNA formula

[cjoly]

Greetings from France,

I would appreciate it if you could have a look at the following formula and tell me what is wrong as I get a message indicating that there is an error in the formula
=IF(ISNA(VLOOKUP(B3;COMPANY2;15;TRUE));"";VLOOKUP
(B3;COMPANY2;15;TRUE))

IN FACT I AM USING A FRENCH VERSION OF EXCEL AND THE ORIGINAL FORMULA IS

=SI(ESTNA(RECHERCHEV(B3;COMPANY2;15;VRAI));"";RECHERCHEV
(B3;COMPANY2;15;VRAI))

Thank you for your help and best regards from Paris,

Christophe

 

[phantom1975]

The formula looks good. Here are the only suggestions I have at this point. Make sure that Company2 has at least 15 columns in it. Try changing the ; to a comma (this usually varies depending on the language of Excel you use and if you have it set up that way in the settings.)

If this doesn't help, then can you offer what part of the formula seems to be erroring?

 

[cjoly]

Thanks for your answer. In fact what is surprising is that the formula

VLOOKUP(B3;COMPANY2;15;TRUE) works perfect. So apparently, it is not the COMPANY2 list that poses problem neither the ; vs ,

Surprinsingly, when I try to validate the whole formula in the cell C3, I get an error message saying that there is an error in the formula.

Best regards,

Christophe

 

[cjoly]

This message was edited by cjoly on 2002-11-12 14:22

 

[cjoly]

This message was edited by cjoly on 2002-11-12 14:21

 

[cjoly]

Sorry I an not trying to build some traffic on MrExcel web site but I run into an unexpected very slow high speed internet connection
This message was edited by cjoly on 2002-11-12 13:51
This message was edited by cjoly on 2002-11-12 14:23

 

[IML]

You could look at this from a different tact. You are not looking for exact match to begin with (True arguemnt) and you want to also want to avoid N/A. N/As are produced when your lookup value is lowest than the lowest value in the table (table Must be sorted).
An alternative formula would be
=IF(CELL("contents",company2)<=B3,VLOOKUP(B3,company2,15),"")

which avoids looking up the the value twice.


EDIT:
Just thought about this, are you using the N/a trap, because there are N/A's in column 15 that, if returned, you want to eliminate? If so, disregard my suggestion.
This message was edited by IML on 2002-11-12 14:33

 

[cjoly]

IML:
Thank you for your thoughts.

I though I replied to you earlier but cannot find my answer on the Forum.
Now I'll do a shorter version.
In fact, I could use False instead of True as I am using a pull down menu (column B) and look up for the information in the list (column A of COMPANY2 located on a different worksheet. Doing this way, I don't have any msorting problem.

The N/A is generated in the cell of column C for which the cell in column B same row are not fill in yet whith data from the pull down menu.

Still my problem is that my formula which seems to be valid on a syntax stanpoint is not validated/refused

Thanks again for your help.

Christophe

 

[IML]

On 2002-11-12 16:08, cjoly wrote:
I could use False instead of True as I am using a pull down menu (column B) and look up for the information in the list (column A of COMPANY2 located on a different worksheet. Doing this way, I don't have any msorting problem.

The N/A is generated in the cell of column C for which the cell in column B same row are not fill in yet whith data from the pull down menu.

I may not be followng, but it sounds like you may be misusing vlookup (outside of the syntax problem). The last argument of "true" or left blank, tells excel to find the closest match, not and exact match, and requires your list be sorted. To find an exact match, you need to change the last argrument to "false" or 0. With the a true argument N/a is returned if the lookup value is lower than the smallest value in your table. With false, n/a is returned if an exact match is not found.

 

[Aladin Akyurek]

On 2002-11-12 13:36, cjoly wrote:
Greetings from France,

I would appreciate it if you could have a look at the following formula and tell me what is wrong as I get a message indicating that there is an error in the formula
=IF(ISNA(VLOOKUP(B3;COMPANY2;15;TRUE));"";VLOOKUP
(B3;COMPANY2;15;TRUE))

IN FACT I AM USING A FRENCH VERSION OF EXCEL AND THE ORIGINAL FORMULA IS

=SI(ESTNA(RECHERCHEV(B3;COMPANY2;15;VRAI));"";RECHERCHEV
(B3;COMPANY2;15;VRAI))

Thank you for your help and best regards from Paris,

Christophe

The formula appears syntactically correct.

However, try the following...

In C3 enter:

=SI(ESTNA(D3);"";D3)

In D3 enter:

=SI(NBCAR(B3);RECHERCHEV(B3;COMPANY2;15;0);"")

I assume COMPANY2 to be a defined name that refers to a data area you want to look in.