Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use INDEX? | Excel Discussion (Misc queries) | |||
Chart axes color index vs font color index | Charts and Charting in Excel | |||
How do I pull the col. index value as well as row index value | Excel Discussion (Misc queries) | |||
Using Index(A:A,.........) | Excel Worksheet Functions | |||
Using INDEX & AND | Excel Worksheet Functions |