Match function

[Antje Crawford]

Hello,
I have the following formula

=MATCH(MID(A138,1,1),A4:A131,0)

which generates a #N/A error. I was able to trace it to
the MID function I am using for a lookup_value argument.
What is the reason that it creates this error? Is there
another solution to it that would work?
All help is greatly appreciated.

TIA.
BR, Antje Crawford

 

[Frank Kabel]

Hi
have you checked that the value returned from the MID function (that is
the first character of cell A138) is in the range A4:A134 (also only as
single character)?

 

[JE McGimpsey]

From XL Help:

If MATCH is unsuccessful in finding a match, it returns the #N/A
error value.

What do you want to happen if the character isn't found in A4:A131?

To return a blank:

=IF(ISNA(MATCH(MID(A138,1,1),A4:A131,0)), "",
MATCH(MID(A138,1,1),A4:A131,0))

 

[Peo Sjoblom]

Just a guess



=MATCH(--(TRIM(MID(A138,1,1)),A4:A131,0)

the above is if it is supposed to be numeric

or

=MATCH(TRIM(MID(A138,1,1)),A4:A131,0)

TRIM will remove leading or trailing (or multiple adjacent) space

 

[Antje Crawford]

Unfortunately, it shouldn't return #N/A, since the first
character of A138 is in range A4:A131 and range A4:A131 is
not empty.
If I use both functions separately, rather then in a
nested format, it returns everything correct.

 

[Guest]

Hi Frank,
I'm not sure if I understand your response correctly.
The range A4:A131 contains letters, numbers, special
characters.
The value returned from the MID function is listed in
range A4:A131.

BR, Antje

 

[Frank Kabel]

Hi Antje
one question: In range A4:A131 the single character (without any other
characters in this cell) exist?

 

[Frank Kabel]

Hi
if you like email me your spreadsheet
(frank[dot]kabel[at]freenet[dot]de) and I'll have a look at it

 

[Ken Wright]

If the value returned by the MID function is a number, eg 7 and that number is
in your list as 7, then it will not find it. MID returns a text string, whereas
7 on it's own in your list is likely to be a number, eg:-

=MID(76543,1,1) = 7, BUT, it will be text!!

Try Peo's solution where he uses the double unary -- to convert numeric
'looking' data back to 'real' numeric data.

 

[Antje Crawford]

I finally got it working ... thanks to the eg from Ken .
Thanks everybody for your help.

One question left ... since the MID function is for text
strings, is there an equivalent function for values?

Thanks again.
BR, Antje

 

[RagDyer]

Actually, it's not *for* (only) text strings, but it *returns* characters
*as* text (strings).

On a new sheet, in A1, enter 12345.
in B1 enter:
=MID(A1,2,3)
Notice that the 234 that's returned is justified left, which means that XL
translates this to be text.

Now, revise the formula to this:
=MID(A1,2,3)*1
OR this:
=--MID(A1,2,3)
And notice that the 234 now is right justified, meaning it's now a number as
far as XL is concerned.

So there's your equivalents, times one (*1),
OR the double unary (--),
as being intricate parts of the function.

As opposed to other, after the fact coersions of the cell, to transform the
text integers to numeric.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I finally got it working ... thanks to the eg from Ken .
Thanks everybody for your help.

One question left ... since the MID function is for text
strings, is there an equivalent function for values?

Thanks again.
BR, Antje