| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of characters in a cell for a Index/match function
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number of characters in a cell for a Index/match function
Can you post an example and the formula you are using..
If this post helps click Yes --------------- Jacob Skaria "Govind" wrote: 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number of characters in a cell for a Index/match function
"=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 "ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABC DEFG ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGA BCDE FGABCDEFG ABCDEFG ABCDEFG" "Jacob Skaria" wrote: Can you post an example and the formula you are using.. If this post helps click Yes --------------- Jacob Skaria "Govind" wrote: 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number of characters in a cell for a Index/match function
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? -- Rick (MVP - Excel) "Govind" <Govind @discussions.microsoft.com wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number of characters in a cell for a Index/match function
Hi,
What result does the INDEX() and MATCH() throw up? -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Govind" wrote in message ... "=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 "ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABC DEFG ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGA BCDE FGABCDEFG ABCDEFG ABCDEFG" "Jacob Skaria" wrote: Can you post an example and the formula you are using.. If this post helps click Yes --------------- Jacob Skaria "Govind" wrote: 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number of characters in a cell for a Index/match function
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 "Govind" wrote: "=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 "ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABC DEFG ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGA BCDE FGABCDEFG ABCDEFG ABCDEFG" "Jacob Skaria" wrote: Can you post an example and the formula you are using.. If this post helps click Yes --------------- Jacob Skaria "Govind" wrote: 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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number of characters in a cell for a Index/match function
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 --------------- Jacob Skaria "Govind" wrote: "=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 "ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABC DEFG ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGA BCDE FGABCDEFG ABCDEFG ABCDEFG" "Jacob Skaria" wrote: Can you post an example and the formula you are using.. If this post helps click Yes --------------- Jacob Skaria "Govind" wrote: 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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number of characters in a cell for a Index/match function
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" wrote: 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 "Govind" wrote: "=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 "ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABC DEFG ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGA BCDE FGABCDEFG ABCDEFG ABCDEFG" "Jacob Skaria" wrote: Can you post an example and the formula you are using.. If this post helps click Yes --------------- Jacob Skaria "Govind" wrote: 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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number of characters in a cell for a Index/match function
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 "Govind" wrote: 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" wrote: 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 "Govind" wrote: "=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 "ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABC DEFG ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGA BCDE FGABCDEFG ABCDEFG ABCDEFG" "Jacob Skaria" wrote: Can you post an example and the formula you are using.. If this post helps click Yes --------------- Jacob Skaria "Govind" wrote: 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? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number of characters in a cell for a Index/match function
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" wrote in message ... "=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 "ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABC DEFG ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGA BCDE FGABCDEFG ABCDEFG ABCDEFG" "Jacob Skaria" wrote: Can you post an example and the formula you are using.. If this post helps click Yes --------------- Jacob Skaria "Govind" wrote: 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? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number of characters in a cell for a Index/match function
Its not working its giving me # value
"Jacob Skaria" wrote: 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 "Govind" wrote: 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" wrote: 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 "Govind" wrote: "=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 "ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABC DEFG ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGA BCDE FGABCDEFG ABCDEFG ABCDEFG" "Jacob Skaria" wrote: Can you post an example and the formula you are using.. If this post helps click Yes --------------- Jacob Skaria "Govind" wrote: 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? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Number of characters in a cell for a Index/match function
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 --------------- Jacob Skaria "Govind" wrote: Its not working its giving me # value "Jacob Skaria" wrote: 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 "Govind" wrote: 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" wrote: 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 "Govind" wrote: "=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 "ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABC DEFG ABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGABCDEFGA BCDE FGABCDEFG ABCDEFG ABCDEFG" "Jacob Skaria" wrote: Can you post an example and the formula you are using.. If this post helps click Yes --------------- Jacob Skaria "Govind" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with function using INDEX and MATCH. | Excel Worksheet Functions | |||
| LOOKUP OR INDEX/MATCH TO FIND NUMBER? | Excel Discussion (Misc queries) | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
| index / match function | Excel Worksheet Functions | |||
Linear Mode
