Article 9 – Overcome WildCard VLOOKUP / MATCH Problem when Target String is more than 255 Characters

Once again, I have got idea to write this article after I responded to this post Vlookup to find URL using wildcard in Excel Microsoft Community.

Before I delve into problem statement, I want to start with some basic information. Maximum column width can be of 255 characters, this means that if I select a column, take right click and select Column Width, I can give a maximum value of 255. If I try to give more than 255, it will immediately give an error message.

Length of cell contents (text) – 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

So, what is 255 vs. 32,767. It means that you will be able to make column to as wide as 255. Now, depending upon the character width, the 255 width cell may contain <255, = 255 or > 255 characters. For example, you type "." and you will find that you can type many characters more than 255, close to 400. Now type "X" and you will find the cell contains less than 255, close to 170.

If a cell can not contain the characters, the characters will spill over into next cells but we can type a maximum of 32,767 characters only in a column. Note – Only 1,024 display in a cell; all 32,767 display in the formula bar.

The Excel file related to this article can be downloaded from 255 Characters.

Now, when you perform VLOOKUP or MATCH for Wildcard search, this fails if Target String is > 255 Characters. This will fail even if your looked up value is contained before 255 limit comes into picture. Look into below snip handling different scenarios.

Solution – To solve wild card search problem, we will have to use SEARCH function.  In Column E above, I have applied SEARCH function to detect look up value in target string and it gives the result irrespective whether the target string is >255 or not.

For VLOOKUP, following formula can be used –

=INDEX($H$2:$H$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A2,$G$2:$G$10)),,),0))

For MATCH, following formula can be used –

=MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A2,$G$2:$G$10)),,),0)

Notice, the result given by above formulas and now row no. 5 is also having result.

You may optionally choose to enclose it within IFERROR block for not found cases and avoid displaying errors.

=IFERROR(INDEX($H$2:$H$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A2,$G$2:$G$10)),,),0)),"")

=IFERROR(MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A2,$G$2:$G$10)),,),0),"")

How Do the Formulas Work

Let's take =MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A2,$G$2:$G$10)),,),0) this first as this is, in anyway, embedded within VLOOKUP formula.

SEARCH(A2,$G$2:$G$10) will look into G2 to G10 for A2 and will produce an array of positions found and #VALUE. ISNUMBER will convert positions found to TRUE and #VALUE TO FALSE. The INDEX before ISNUMBER forces SEARCH to return an Array. Now When MATCH is applied for TRUE on this Array, it returns the position in G2 to G10.

Now for VLOOKUP, the values are extracted using the range with this position found in above match formula.

See the evaluation steps –