Index and #NA
 

From another post, I adapted a similar formula to my needs as follows:
=INDEX($P$2:$P$35000,MATCH(1,INDEX(($L$2:$L$35000= $A5)*($D5=$I$1)*($O$2:$O$35000=$D5),),)).
Since the data in column D varies but is unique to that row, I repeat the
formula (with the variations) in a few columns. Basically, I am matching 3
criteria to get a text result from column P.
What I am now trying to do is prefix the formula with the "If(ISNA: function
to eliminate the no match. However, I can't figure out where the ,0 or " "
after the first formula should go. Depending on where I have tried to place
the 'blank' I get either: Too many arguments, Too few arguments or missing
parenthesis error.

Thanks in advance,

Ron Rosenberg

Index and #NA
 

That formula would be very inefficient (and long) with a "standard" error
trap:

=IF(ISNA(MATCH(1,INDEX(($L$2:$L$35000=$A5)*($D5=$I $1)*($O$2:$O$35000=$D5),),)),"",INDEX($P$2:$P$3500 0,MATCH(1,INDEX(($L$2:$L$35000=$A5)*($D5=$I$1)*($O $2:$O$35000=$D5),),)))

Try it like this:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX($P$2:$P$3500 0,MATCH(1,INDEX(($L$2:$L$35000=$A5)*($D5=$I$1)*($O $2:$O$35000=$D5),),))))

--
Biff
Microsoft Excel MVP


"ronnomad" wrote in message
...
From another post, I adapted a similar formula to my needs as follows:
=INDEX($P$2:$P$35000,MATCH(1,INDEX(($L$2:$L$35000= $A5)*($D5=$I$1)*($O$2:$O$35000=$D5),),)).
Since the data in column D varies but is unique to that row, I repeat the
formula (with the variations) in a few columns. Basically, I am matching
3
criteria to get a text result from column P.
What I am now trying to do is prefix the formula with the "If(ISNA:
function
to eliminate the no match. However, I can't figure out where the ,0 or "
"
after the first formula should go. Depending on where I have tried to
place
the 'blank' I get either: Too many arguments, Too few arguments or missing
parenthesis error.

Thanks in advance,

Ron Rosenberg

Index and #NA
 

As always, you guys rock. Works like a charm. The only problem, with the
size of the file, after 6 or 7 minutes it's only 10% done. I'm cobbling this
because the data is not available off our main data files.

This is going to same someone hours of work

Thanks,

Ron

"T. Valko" wrote:

That formula would be very inefficient (and long) with a "standard" error
trap:

=IF(ISNA(MATCH(1,INDEX(($L$2:$L$35000=$A5)*($D5=$I $1)*($O$2:$O$35000=$D5),),)),"",INDEX($P$2:$P$3500 0,MATCH(1,INDEX(($L$2:$L$35000=$A5)*($D5=$I$1)*($O $2:$O$35000=$D5),),)))

Try it like this:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX($P$2:$P$3500 0,MATCH(1,INDEX(($L$2:$L$35000=$A5)*($D5=$I$1)*($O $2:$O$35000=$D5),),))))

--
Biff
Microsoft Excel MVP


"ronnomad" wrote in message
...
From another post, I adapted a similar formula to my needs as follows:
=INDEX($P$2:$P$35000,MATCH(1,INDEX(($L$2:$L$35000= $A5)*($D5=$I$1)*($O$2:$O$35000=$D5),),)).
Since the data in column D varies but is unique to that row, I repeat the
formula (with the variations) in a few columns. Basically, I am matching
3
criteria to get a text result from column P.
What I am now trying to do is prefix the formula with the "If(ISNA:
function
to eliminate the no match. However, I can't figure out where the ,0 or "
"
after the first formula should go. Depending on where I have tried to
place
the 'blank' I get either: Too many arguments, Too few arguments or missing
parenthesis error.

Thanks in advance,

Ron Rosenberg

Index and #NA
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ronnomad" wrote in message
...
As always, you guys rock. Works like a charm. The only problem, with the
size of the file, after 6 or 7 minutes it's only 10% done. I'm cobbling
this
because the data is not available off our main data files.

This is going to same someone hours of work

Thanks,

Ron

"T. Valko" wrote:

That formula would be very inefficient (and long) with a "standard" error
trap:

=IF(ISNA(MATCH(1,INDEX(($L$2:$L$35000=$A5)*($D5=$I $1)*($O$2:$O$35000=$D5),),)),"",INDEX($P$2:$P$3500 0,MATCH(1,INDEX(($L$2:$L$35000=$A5)*($D5=$I$1)*($O $2:$O$35000=$D5),),)))

Try it like this:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX($P$2:$P$3500 0,MATCH(1,INDEX(($L$2:$L$35000=$A5)*($D5=$I$1)*($O $2:$O$35000=$D5),),))))

--
Biff
Microsoft Excel MVP


"ronnomad" wrote in message
...
From another post, I adapted a similar formula to my needs as follows:
=INDEX($P$2:$P$35000,MATCH(1,INDEX(($L$2:$L$35000= $A5)*($D5=$I$1)*($O$2:$O$35000=$D5),),)).
Since the data in column D varies but is unique to that row, I repeat
the
formula (with the variations) in a few columns. Basically, I am
matching
3
criteria to get a text result from column P.
What I am now trying to do is prefix the formula with the "If(ISNA:
function
to eliminate the no match. However, I can't figure out where the ,0 or
"
"
after the first formula should go. Depending on where I have tried to
place
the 'blank' I get either: Too many arguments, Too few arguments or
missing
parenthesis error.

Thanks in advance,

Ron Rosenberg