index, Match statement
 

Looking for some help, using this formula
=INDEX($N$2:$N$24,MATCH(1,INDEX(($L$2:$L$24=$L28)* ($O$2:$O$24=N$27),),))
(which I'm a novice at) I'm able to retrieve an answer if it is in the lookup
table but if the query does not it returns an error. How would I or is there
a way to do this and if the result does not find a value to either leave the
field blank or post a 0
Thanks,

Below is the reference sheet I was working with on this formula I'm matching
the date and workcell.

Lookup range:
Date Used Workcell
8/20/2009 1 6
8/20/2009 1 11
8/21/2009 1 5
8/21/2009 1 6
8/21/2009 1 11
8/24/2009 1 8
8/24/2009 1 11


Populating a table
5 6 8 11
8/19/2009 #N/A #N/A #N/A #N/A
8/20/2009 #N/A 1 #N/A 1
8/21/2009 1 1 #N/A 1
8/24/2009 #N/A #N/A 1 1

index, Match statement
 

Try this to return 0 instead of the error:

=LOOKUP(1E100,CHOOSE({1,2},0,INDEX($N$2:$N$24,MATC H(1,INDEX(($L$2:$L$24=$L28)*($O$2:$O$24=N$27),),)) ))

Note that this will only work if the return value is numeric.

--
Biff
Microsoft Excel MVP


"Bob" wrote in message
...
Looking for some help, using this formula
=INDEX($N$2:$N$24,MATCH(1,INDEX(($L$2:$L$24=$L28)* ($O$2:$O$24=N$27),),))
(which I'm a novice at) I'm able to retrieve an answer if it is in the
lookup
table but if the query does not it returns an error. How would I or is
there
a way to do this and if the result does not find a value to either leave
the
field blank or post a 0
Thanks,

Below is the reference sheet I was working with on this formula I'm
matching
the date and workcell.

Lookup range:
Date Used Workcell
8/20/2009 1 6
8/20/2009 1 11
8/21/2009 1 5
8/21/2009 1 6
8/21/2009 1 11
8/24/2009 1 8
8/24/2009 1 11


Populating a table
5 6 8 11
8/19/2009 #N/A #N/A #N/A #N/A
8/20/2009 #N/A 1 #N/A 1
8/21/2009 1 1 #N/A 1
8/24/2009 #N/A #N/A 1 1

index, Match statement
 

Hi,

Try this

=if(iserror(INDEX($N$2:$N$24,MATCH(1,INDEX(($L$2:$ L$24=$L28)*($O$2:$O$24=N$27),),))),"",INDEX($N$2:$ N$24,MATCH(1,INDEX(($L$2:$L$24=$L28)*($O$2:$O$24=N $27),),)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Bob" wrote in message
...
Looking for some help, using this formula
=INDEX($N$2:$N$24,MATCH(1,INDEX(($L$2:$L$24=$L28)* ($O$2:$O$24=N$27),),))
(which I'm a novice at) I'm able to retrieve an answer if it is in the
lookup
table but if the query does not it returns an error. How would I or is
there
a way to do this and if the result does not find a value to either leave
the
field blank or post a 0
Thanks,

Below is the reference sheet I was working with on this formula I'm
matching
the date and workcell.

Lookup range:
Date Used Workcell
8/20/2009 1 6
8/20/2009 1 11
8/21/2009 1 5
8/21/2009 1 6
8/21/2009 1 11
8/24/2009 1 8
8/24/2009 1 11


Populating a table
5 6 8 11
8/19/2009 #N/A #N/A #N/A #N/A
8/20/2009 #N/A 1 #N/A 1
8/21/2009 1 1 #N/A 1
8/24/2009 #N/A #N/A 1 1