One more "LOOKUP" question

Posted by Mike Wortman on July 20, 2001 7:26 AM

Aladin provided me with the formula below to accomplish a little task I had on a scheduling template.

=VLOOKUP(E1,$A$2:$B$8,2,0)

My question: Is there a simple way to alter this formula to force it to return a 0(zero)instead of #N/A if it does not find a matching text string in the table?

Posted by Barrie Davidson on July 20, 2001 8:01 AM

Try:
=IF(ISNA(VLOOKUP(E1,$A$2:$B$8,2,0)),0,VLOOKUP(E1,$A$2:$B$8,2,0))

Regards,
Barrie

Posted by Eric on July 20, 2001 8:09 AM

Also look at Aladin's discussion in a previous thread

I have a workbook with several sheets. The first sheet is for raw data, which we'll call CALLS. Each record in this table has a SUBJECT field.

The other sheets are named for the types of SUBJECTS. For example, I have a MICROSOFT OFFICE worksheet, where records with SUBJECTS like Word, Excel, Powerpoint, and Frontpage would go.

The question is: How do I (using formulae or VBA) pull or copy all the records in my CALLS table from my raw data sheet that have a specific subject. This would be almost like an advanced filter, except I need the results on different sheets. I guess I can use a macro to do several Advanced Filters against my data and then copy (or move) the results to different sheets. Would this work?

I usually have between 10000 and 12000 records in my CALLS table. I can put the list of SUBJECTS that belong on each sheet anywhere. I'd like to have them on the sheet that the records would get copied to.