CODEWITHSUNDEEP
vbaexcel
Matthias94
Matthias94

Reputation: 47

Char limit on vlookup AND index/match combination

I created a table/sheet of items consolidated from different lists. All items of the different lists are added and then duplicates are deleted. Then i add a empty column for each of the single lists after the first column. I used vlookup to write "X" in every row of the corresponding column if the item is in this specific list. Each list has a own sheet where i previously wrote an extra column with X in every row, so vlookup can output the 2nd column containing just the x's. At the end i get a table of which list has the item, q

The formula i used: =Iferror(vlookup(A2;'sheetListA'!A:B;2;false);" ")

Apparently vlookup stops working if the looked up value contains more than 255 characters (across different sheets). I now tried for quite a while to rebuild my formula with INDEX and MATCH but without success. I managed to make a working formula but it has the same problem as vlookup and stops working when there are more than 255 chars in the cell.

Another formula not working: =INDEX('630'!A:B;MATCH(A02;'sheetListA'!A:A;0);2)

Upvotes: 1

Views: 2551

Answers (2)

ntselama
ntselama

Reputation: 1

Here is a custom VLOOKUP like function that can search characters over 255 characters.

Function VLOOKUPPLUS(SearchCell, LookUpArray As Range, ColNum As Long) As Variant
For Each cell In LookUpArray.Columns(1).Cells
If cell = SearchCell Then
VLOOKUPPLUS = cell.Offset(0, ColNum - 1)
Exit For
End If
VLOOKUPPLUS = "N/A"
Next
End Function

Upvotes: 0

Matthias94
Matthias94

Reputation: 47

Rory aswered my question in the comments:

Try: =LOOKUP(2;1/(A2='sheetListA'!A2:A1000);'sheetListA'!B2:B1000) Do not use entire column references

. – Rory Aug 16 at 12:56

Change sheetListA to whatever your sheet name actually is.

Rory Aug 16 at 14:50

Upvotes: 0

Related Questions