Shooting Blanks

[Cruiser]

Objective:
1) Mid search a text string,
2) Search for the result in column 1 of a named list and return the value of the corresponding cell in column 2,
3) If the result is an error, return a blank.

I am using this combination of ISERROR, MID SEARCH, and VLOOKUP:

=IF(ISERROR(VLOOKUP(MID($A1,SEARCH(";",$A1,1)+1,SEARCH(";",$A1,SEARCH(";",$A1,1)+1)-SEARCH(";",$A1,1)),List,2,FALSE)),"",VLOOKUP(MID($A1,SEARCH(";",$A1,1)+1,SEARCH(";",$A1,SEARCH(";",$A1,1)+1)-SEARCH(";",$A1,1)),List,2,FALSE))

It works perfectly. However, the workbook is not opening as fast as I would like. Does anybody have a more elegant (shorter, faster) formula for accomplishing the stated objective?

[Tony Valko]

If the result is a TEXT value you can try this...

=LOOKUP("zzzzz",CHOOSE({1,2},"",VLOOKUP(MID($A1,SEARCH(";",$A1,1)+1,SEARCH(";",$A1,SEARCH(";",$E4,1)+1)-SEARCH(";",$A1,1)),List,2,0)))

[Cruiser]

Thanks for the idea. However, I do not want to change the objective to search for a particular string of text. I actually had something similar before, and greatly reduced the calculation time by doing this "universal" mid search rather than specific text string searches. The text strings being searched have a variable number of delimiters, and sometimes result in an error. That is fine, but I want an error to return a blank. The only way I could make it work was by adding the iserror.

I am wondering if there is a faster way to accomplish the stated objective without modifying that objective in any way. I wouldn't mind changing the formula completely if it would accomplish the objective exactly as stated. Again, thanks for your input.

[shg]

=IFERROR(VLOOKUP(MID($A1,SEARCH(";",$A1,1)+1,SEARCH(";",$A1,SEARCH(";",$A1,1)+1)-SEARCH(";",$A1,1)),List,2,FALSE),"")

[Tony Valko]

The formula I suggested is not modifying your objective in any way.

It's just using a much more efficient error trap.

Since your profile says you're using Excel 2003 this is about as efficient as it can get.

This:

LOOKUP("zzzzz",CHOOSE({1,2},"",

Does the same thing as this (sort of!):

IF(ISERROR(VLOOKUP(MID($A1,SEARCH(";",$A1,1)+1,SEARCH(";",$A1,SEARCH(";",$A1,1)+1)-SEARCH(";",$A1,1)),List,2,FALSE)),"",

[shg]

Since your profile says you're using Excel 2003 ...

Missed that. +1 for Tony's suggestion.

[Cruiser]

Thanks SHG, but this formula returns an error.

[Cruiser]

Thanks Tony. I thought that was searching for a text-sub string of z's. However, it was actually the magical formula I was looking for. You are the man.

[Tony Valko]

You're welcome. Thanks for the feedback!