Vlookup restricted to 255 String Search ?

hatman

Well-known Member

Joined

Apr 8, 2005

Messages

2,664

• Sep 12, 2006

• #2

Try this:

Book1
	B	C	D	E
2	sallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjessesallyjesses	red
3	johndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoe	orange
4	dilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbertdilbert	yellow
5
6
7
8	orange
9	johndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoejohndoe
Sheet2

=INDEX(C:C,MAX(IF(B2:B4=B9,ROW(B2:B4),0))) confirmed with Ctrl+Shift+Enter

 

Upvote 0

S

SamBo1234

Board Regular

Joined

Aug 21, 2006

Messages

77

• Sep 12, 2006

• #3

Hi There,

Thanks for your reply but it doesnt solve my problem, let me make it a bit clearer.

SHEET 1

I have Strings of text between 0 and 600 in length in column A (Approximatley 1000 rows)

SHEET 2

I have the exact same strings of text in Sheet 2 (different order), in column A but in column B it has references relating to that data. I.e Numbers, Text etc

I want to be able to return the Value in Column B, in Sheet 2, in Column B, Sheet 1 if there is a Match.

Easy enough to do with vlookup however, i cant get around the restriction where vlookup only alows max 255 character string searchs without having to mess about with the data.

Regards
Sam

 

Upvote 0

NBVC

Well-known Member

Joined

Aug 31, 2005

Messages

5,828

• Sep 12, 2006

• #4

I tried Hatman's solution and it seems to do what you asked....

This is the formula with the sheet names included. Remember you must confirm it with CTRL+SHIFT+ENTER not just Enter..you'lll see curly brackets at the ends of the formula if you confirmed it correctly...

=INDEX(Sheet2!B:B,MAX(IF(Sheet2!A2:A1000=A2,ROW(Sheet2!A2:A1000),0)))

where A2 bolded is the lookup value in Sheet1.

adjust the ranges to suit and copy down.

 

Upvote 0

S

SamBo1234

Board Regular

Joined

Aug 21, 2006

Messages

77

• Sep 12, 2006

• #5

Hello Again, Thanks for the help guys really appreciate it,

I still seem to have problems, i have used the formula as given, and used CTRL-SHIFT-ENTER to confirm it however the values are returning as #NUM! for each entry.

This is what my formula is.

{=INDEX('Test Data'!B:B,MAX(IF('Test Data'!A:A='Server Error Data'!G2,ROW('Test Data'!A:A),0)))}

Test Data im assuming represents Sheet2
Server Error Data represents Sheet1

This correct ?

 

Upvote 0

hatman

Well-known Member

Joined

Apr 8, 2005

Messages

2,664

• Sep 12, 2006

• #6

Please post your exact formula.

Also verify that you have not referenced the entire column in the Max() function... in other words =INDEX(C:C,MAX(IF(B:B=E9,ROW(B:B),0))) will result in a #NUM! error, while =INDEX(C:C,MAX(IF(B2:B4=E9,ROW(B2:B4),0))) works fine... it's just the way that Array formulas behave.

 

Upvote 0

S

SamBo1234

Board Regular

Joined

Aug 21, 2006

Messages

77

• Sep 12, 2006

• #7

Ahhhhhhhh, thats why then.

Sorry i have referenced the entire columns.

I didnt know u had to be specific in the range of cells in the formula, Sorry guys, im just beginning to grasp this sort of stuff

I have now tryed it with selected ranges and it works!....

Many thanks all sorry it took so long to figure it out

Cheers

 

Upvote 0

hatman

Well-known Member

Joined

Apr 8, 2005

Messages

2,664

• Sep 12, 2006

• #8

Ahhhhhhhh, thats why then.

Sorry i have referenced the entire columns.

I didnt know u had to be specific in the range of cells in the formula, Sorry guys, im just beginning to grasp this sort of stuff

I have now tryed it with selected ranges and it works!....

Many thanks all sorry it took so long to figure it out

Cheers

Click to expand...

HAH! Took me a lot longer than that to figure it out for myself No harm, no foul, that's why we are here: to help!

Array formulas are funny things, and they require discreet ranges for iteration to operate porperly... on the OTHER hand, a full column can be referenced within an array formula, as long as it is the argument for a function that CAN deal with a full column... hence the Index() function works fine in yoru example when it references a full column.

If your data may vary in length, thus reulting in varying references that may need updating, you can consider creating a Dynamic Range instead... just a thought.

 

Upvote 0

Y

yoked

New Member

Joined

Sep 18, 2006

Messages

2

• Sep 18, 2006

• #9

Hi hatman,

thanks for your solution for the above problem. I have a similar problem, but it's a little bit special and I'm not able to solve it.

It would be great, if you can take a look:

I want to search for the string "xxx" in the cells B2:B4. Therefore I have added to B4 the string "xxx". And changed the formula like follows:
=INDEX(C:C;MAX(IF(B2:B4="*"&B9&"*";ROW(B2:B4);0)))

However I don't get back the value "grey". Instead of that I get back only "0".

Do you know why? Hopefully you have a solution for my problem, or anybody else.

Thanks in advance.

 

Upvote 0

hatman

Well-known Member

Joined

Apr 8, 2005

Messages

2,664

• Sep 18, 2006

• #10

Welcome to the board!

I assume that you have implemented this method because you have values in your table that exceed 255 characters. Unfortunately this method does not support the use of wildcards... only the 3 Lookup() functions can do that. However, the following change can be made to provide a match when the search string is found within the strings contained in the specified range.

Code:

=INDEX(C:C,MAX(IF(ISNUMBER(SEARCH(B9,B2:B4)),ROW(B2:B4),0)))

 

Upvote 0