255 character limit on MATCH() function - way to bypass?

[George]

Hi guys,

I'm trying to perform MATCH on some long strings, and I have discovered there is an upper limit of 255 characters as the search criteria.

Basically I've made a document creates an automatic summary of some source data (the names of the fields, the length of the longest entry, the data type and the number of entries) using some macros.
I found (I could be wrong about this) that just getting the macro to populate some cells with standard excel formulas (offset to define a range, match to lookup inside it) gave me a result far faster than using a

for each cell in range
    if cell.value = search value then
        tell me where the match is
    end if
next cell

type of statement. Annoyingly I didn't create the data we're working with (otherwise there wouldn't be any need to search for anything so long), and I need to be able to search for the entire string (so no trimming off to the first 255 or something).

Am I going to need to go the macro route, or is there a clever little workaround to this problem?

Cheers,

George

 

[Luke M]

Depending on how big the array is that you want to search, perhaps this formula structure?
=MAX((A1:A10=B2)*(ROW(A1:A10)))

Confirm the formula as an array using Ctrl+Shift+Enter, or use the VB script:
Selection.FormulaArray =

 

[George]

cheers Luke, but I can't work out exactly what that does?

 

[Luke M]

Build 2 arrays. First will be a True/False array checking if the cells in the array match our source cell. Nice thing is that this comparison uses all of the cell's contents, not just first 255 characters. Second array is all the row numbers. Multiples the two arrays to get a single array of 0's and the 1 row number (True = 1, False = 0). The MAX then looks at the array and returns the row number for where your data is at. This could then be used in some other function or macro to give further info.

 

[Marc L]

​

Hi George,​

​

try within the worsheet and using the Macro Recorder …​

​

Like this I had the tip to bypass this limit for others commands.​

 

[George]

Luke, that's awesome! Also, I had no idea you could multiply by a TRUE/FALSE value. So many instances of -- to cut out!

Thanks.