Error in ISNA formula

cjoly

New Member
Joined
Nov 6, 2002
Messages
22
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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

New Member
Joined
Nov 6, 2002
Messages
22
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

New Member
Joined
Nov 6, 2002
Messages
22

ADVERTISEMENT


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

cjoly

New Member
Joined
Nov 6, 2002
Messages
22
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743

ADVERTISEMENT

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

New Member
Joined
Nov 6, 2002
Messages
22
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top