Does VLOOKUP not work if too many characters in cell?

forgetso

forgetso

Board Regular
Joined
Sep 18, 2007
Messages
198
I have a VLOOKUP formula that works when searching some cells but not others when both cases should work.

Q: Does VLOOKUP have a certain maximum amount of characters it can search? If a cell contains more than this amount of characters will VLOOKUP not work?

This is what seems to be happening on my spreadsheet. The VLOOKUPS that refer to the cells with more than the average amount of characters seem to fail. The same happens with INDEX.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
No, that can't be true as I just found a VLOOKUP that failed when the cell it should have found did not contain much...
 
Upvote 0
FORMULA is:

=VLOOKUP("*"&B2&"*",'Press Schedule'!$A$3:$P$297,2,FALSE)

An example of a search term that works is:

"Abergavenny Free Press"

VLOOKUP finds this value in a cell containing the following:

South Wales Argus (Wed & Sat), Blackwood Campaign, Tredegar Campaign, Caerphilly Campaign (Fri), Newport Weekly Argus (Fri), Chepstow, Pontypool, Abergavenny Free Press, Monmouth Free Press (Wed)

An example of a search term that doesn't work is:

"Portsmouth News"

VLOOKUP doesn't find this despite it being contained within the following cell:

Portsmouth News Series - Portsmouth News (Thurs), Central South News (Thurs), Fareham & Gosport News (Thurs), Havant & Waterlooville News (Thurs); Portsmouth Journal (Thurs) & Post Series - Fareham & Gosport Journal (Thurs), Havant & Waterlooville Journal (Thurs), Hamble Valley Journal (Thurs), Bordon Post (wed), Petersfield post (Wed), Jobs Today

I am currently manually working through the one's that VLOOKUP has failed on. May I point out that INDEX also has the same problems.
 
Upvote 0
Yes, I don’t think VLOOKUP or MATCH will work where you have more than 255 characters in the cells, try

=INDEX('Press Schedule'!$B$3:$B$297,MIN(IF(ISNUMBER(SEARCH(B2,'Press Schedule'!$A$3:$A$297)),ROW('Press Schedule'!$A$3:$A$297)-ROW('Press Schedule'!$A$3)+1)))

confirmed with CTRL+SHIFT+ENTER

or

=LOOKUP(2,1/SEARCH(B2,'Press Schedule'!$A$3:$A$297),'Press Schedule'!$B$3:$B$297)

The second of these finds the LAST match. Of course, if you only expect 1 match this will do the same as the above
 
Upvote 0
It seems there is a maximum of 255 characters when using wildcards. This works:

Portsmouth News Series - Portsmouth News (Thurs), Central South News (Thurs), Fareham & Gosport News (Thurs), Havant & Waterlooville News (Thurs); Portsmouth Journal (Thurs) & Post Series - Fareham & Gosport Journal (Thurs), Havant & Waterlooville Journal
 
Upvote 0
Thankyou Andrew for finding that out. Unfortunately that means there is no workaround for me this time. Hopefully the data I receive will be more cleverly set out next time.
 
Upvote 0
forgetso

Have you ever actually considered seperating this data out?

That could easily be done using Data>Text to columns... or perhaps formulas or even code.

Just because you've been given the data in a bad format doesn't mean you need to work with it in that format.:)
 
Upvote 0
Unfortunately that means there is no workaround for me this time. Hopefully the data I receive will be more cleverly set out next time.
Click to expand...

Did you try the formulas I suggested above?
 
Upvote 0
Code: 
=LOOKUP(9.99999999999999E+307,
               SEARCH(","&B2&",",","&Press Schedule'!$A$3:$A$297&","),
               Press Schedule'!$B$3:$B$297)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
interesting vlookup result
Replies
8
Views
339
RachelN76
R
H
  • Solved
Using Macro/VLOOKUP to return format of cell
Replies
9
Views
188
mumps
mumps
M
  • Question
VLOOKUP help - return value if two partial matches are met (I think??)
Replies
5
Views
351
MountainFog
M
SandsB
  • Question
8192 character count limit wasn't there until my files were moved to Onedrive from a Shared Drive
Replies
6
Views
531
SandsB
SandsB
J
  • Question
Calculation on a non-blank cell in a horizontal range
Replies
4
Views
278
JSchell
J

Forum statistics

Threads
1,215,635
Messages
6,125,946
Members
449,275
Latest member
jacob_mcbride

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top