Match function

  • Thread starter Thread starter Antje Crawford
  • Start date Start date
A

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
 
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)?
 
From XL Help:
If MATCH is unsuccessful in finding a match, it returns the #N/A
error value.
Click to expand...

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))
 
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
 
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.
 
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
 
Hi Antje
one question: In range A4:A131 the single character (without any other
characters in this cell) exist?
 
Hi
if you like email me your spreadsheet
(frank[dot]kabel[at]freenet[dot]de) and I'll have a look at it
 
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.
 
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
 
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
 
Back
Top