Number of characters in a cell for a Index/match function

[Govind]

In a index or match function if I have almost 40 characters it does not give
me the values am looking for. Is there a limit on the characters? if Yes,
then whats the solution?

 

[Jacob Skaria]

Can you post an example and the formula you are using..

If this post helps click Yes

 

[Govind]

"=INDEX(Rawdata,MATCH(Q24,'Raw Data'!BR15:BR1226,0),MATCH(Common!$A$7,'Raw
Data'!$A$1:$BR$1,0))" formula

Cell "Q24" is a text which has almost 40 characters which is the only common
field in the data for which I am looking for.

Q24 "ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFG
ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDE FGABCDEFG ABCDEFG
ABCDEFG"

 

[Rick Rothstein]

It is kind of hard to tell you what the solution to your problem is as you
haven't told us anything about your setup. Can you give us a couple of
example cell contents, tell us what you are trying to do with those cell
contents, tell us the formula you are attempting to use and tell us what you
are hoping to have the formula return to you?

 

[Ashish Mathur]

Hi,

What result does the INDEX() and MATCH() throw up?

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jacob Skaria]

Can you try this and get back...Enter the text you have posted earlier in
cell A12. Copy the cell contents to B1. Enter the below formula in C1 and
check whether it is returning the row number 12 or not..

=MATCH(B1,A1:A25,0)

If this post helps click Yes

 

[Jacob Skaria]

I assume 'Rawdata' is the sheet name. You need to refer the range for INDEX..

= INDEX(RawData!<Range>,MATCH...........,MATCH........)


If this post helps click Yes

 

[Govind]

Jacob: Its working and giving me reference 12 only upto a certain number of
characters. The movement i increase the number to 60/70 characters it gives
me #value.

Rawdata: Range that i Have defined for the data.

 

[Jacob Skaria]

It should work upto 255 characters in XL2003... Please check your data for
any carriage returns/breaks..

You can try out this by entering
A12 = REPT("a",255)
B1 = REPT("a",255)

If this post helps click Yes

 

[Ashish Mathur]

Hi,

It could be the Q24 is actually not in range 'Raw Data'!BR15:BR1226. To do
a quick check, insert the text in cell Q24 in the Find box and see if it
highlights any cell

MATCH(Q24,'Raw Data'!BR15:BR1226,0),

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Govind]

Its not working its giving me # value

It should work upto 255 characters in XL2003... Please check your data for
any carriage returns/breaks..

You can try out this by entering
A12 = REPT("a",255)
B1 = REPT("a",255)

If this post helps click Yes

 

[Jacob Skaria]

1. What version of Excel you are using

2. Do you really mean to say the formula =MATCH(B1,A$1:A$25,0) with the
below test data fails..or return the row number 12.
A12 = REPT("a",255)
B1 = REPT("a",255)

3. In your formula you have not mentioned the range for Index 'RawData
= INDEX(RawData!<Range>,MATCH...........,MATCH........)


If this post helps click Yes