hiding an error value, using INDEX/MATCH
 

Hi,

I am using the INDEX/MATCH combination to look for data in some source
files. In case a value is missing the formula will return an error value":
#N/A. I know it is possible to hide the error values using =IF(ISERROR)€¦.etc.

I just dont get it right in combination with the INDEX/MATCH function I am
currently using.

I am using:

=INDEX(array;row_num;column_num)

An example:

=INDEX('D:\Marieke Test\TEST BESTANDEN\[TEST
PROTECTION.xls]Sheet1'!$A$1:$AD$418;MATCH(E3;'D:\Marieke Test\TEST
BESTANDEN\[TEST
PROTECTION.xls]Sheet1'!$R$1:$R$418;0);MATCH("merkstam*";'D:\Marie ke Test\TEST
BESTANDEN\[TEST PROTECTION.xls]Sheet1'!$A$1:$AD$1;0))

Can somebody tell me how to incorporate the IF(ISERROR)€¦so that if the
formula would return an error value, instead it will show a blank in that
cell?

Thanks a lot!

Marieke

hiding an error value, using INDEX/MATCH
 

Hi

If you want to use If(Iserror), then the formula construction would be
=IF(ISERROR(your_Formula);"";your_Formula)

If you have XL2007, you would use
=IFERROR(your_Formula,"")

--
Regards
Roger Govier

"mariekek5" wrote in message
...
Hi,

I am using the INDEX/MATCH combination to look for data in some source
files. In case a value is missing the formula will return an error value":
#N/A. I know it is possible to hide the error values using
=IF(ISERROR)€¦.etc.

I just dont get it right in combination with the INDEX/MATCH function I
am
currently using.

I am using:

=INDEX(array;row_num;column_num)

An example:

=INDEX('D:\Marieke Test\TEST BESTANDEN\[TEST
PROTECTION.xls]Sheet1'!$A$1:$AD$418;MATCH(E3;'D:\Marieke Test\TEST
BESTANDEN\[TEST
PROTECTION.xls]Sheet1'!$R$1:$R$418;0);MATCH("merkstam*";'D:\Marie ke
Test\TEST
BESTANDEN\[TEST PROTECTION.xls]Sheet1'!$A$1:$AD$1;0))

Can somebody tell me how to incorporate the IF(ISERROR)€¦so that if the
formula would return an error value, instead it will show a blank in that
cell?

Thanks a lot!

Marieke

hiding an error value, using INDEX/MATCH
 

=IF(ISERROR(your-formula),"", your-formula
That is a pair of double-quotes with nothing between them

But it is easier in Excel 2007
IFERROR(your-formula, "")

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"mariekek5" wrote in message
...
Hi,

I am using the INDEX/MATCH combination to look for data in some source
files. In case a value is missing the formula will return an error value":
#N/A. I know it is possible to hide the error values using
=IF(ISERROR)..etc.

I just don't get it right in combination with the INDEX/MATCH function I
am
currently using.

I am using:

=INDEX(array;row_num;column_num)

An example:

=INDEX('D:\Marieke Test\TEST BESTANDEN\[TEST
PROTECTION.xls]Sheet1'!$A$1:$AD$418;MATCH(E3;'D:\Marieke Test\TEST
BESTANDEN\[TEST
PROTECTION.xls]Sheet1'!$R$1:$R$418;0);MATCH("merkstam*";'D:\Marie ke
Test\TEST
BESTANDEN\[TEST PROTECTION.xls]Sheet1'!$A$1:$AD$1;0))

Can somebody tell me how to incorporate the IF(ISERROR).so that if the
formula would return an error value, instead it will show a blank in that
cell?

Thanks a lot!

Marieke

hiding an error value, using INDEX/MATCH
 

=IF(ISNA(<match formula),"",<index with match formula)

or in other words
If no match found blank; otherwise go with the INDEX

If this post helps click Yes
---------------
Jacob Skaria


"mariekek5" wrote:

Hi,

I am using the INDEX/MATCH combination to look for data in some source
files. In case a value is missing the formula will return an error value":
#N/A. I know it is possible to hide the error values using =IF(ISERROR)€¦.etc.

I just dont get it right in combination with the INDEX/MATCH function I am
currently using.

I am using:

=INDEX(array;row_num;column_num)

An example:

=INDEX('D:\Marieke Test\TEST BESTANDEN\[TEST
PROTECTION.xls]Sheet1'!$A$1:$AD$418;MATCH(E3;'D:\Marieke Test\TEST
BESTANDEN\[TEST
PROTECTION.xls]Sheet1'!$R$1:$R$418;0);MATCH("merkstam*";'D:\Marie ke Test\TEST
BESTANDEN\[TEST PROTECTION.xls]Sheet1'!$A$1:$AD$1;0))

Can somebody tell me how to incorporate the IF(ISERROR)€¦so that if the
formula would return an error value, instead it will show a blank in that
cell?

Thanks a lot!

Marieke