#N/A error on lookup

Morning all

Apologies if there is a thread relating to this already but the ones I have found are all slightly different to my problem.

Column A is a course name. Column B is length of course. I then have reference columns which we'll call C and D. C lists all the courses and D has their corresponding course length next to it.

The idea is that I can type the course name into A and B will display the length. However, if no course is listed in A, then I get the #N/A error displayed instead.

I have tried the various ISERROR options and I then get #!NAME errors.

The formula I am using in B1 is =lookup(A1,C1:C5,D1:D5)

When I put the course name in A1, the correct length appears in B1 so I know the basic formula works.

P.S. I can't get conditional Formatting to hide the error either.

Many thanks and look forward to hearing some good news.

Try this...

Formula:

Please Login or Register  to view this content.

My Suggestion instead of lookup

Formula:

Please Login or Register  to view this content.

hi CTCoperations, an alternative:
=IF(COUNTIF(C1:C5,A1),VLOOKUP(A1,C1:D5,2,0),"")

In C20

=IF(ISNA(INDEX(CREW!C:C,MATCH($A20,CREW!$A:$A,0))),"",INDEX(CREW!C:C,MATCH($A20,CREW!$A:$A,0)))

Copy down and across