MATCH not working

[David]

I'm trying to look up the relative position of an integer in an array
using the MATCH function. The array is on a worksheet titled 'pluto
ids' and the lookup_value is in a cell based on a formula in the
worksheet titled 'downloads'.

When I use the cell reference in the MATCH, I don't get any return
(result = #N/A). When I use the calculated integer itself in the
MATCH, it works just fine.

Here's the formula with the error: =MATCH(B8,'pluto ids'!A2:A66,0)
Here's the formula that results in the lookup_value (not that it
should matter): =RIGHT(RIGHT(A8,5),LEN(RIGHT(A8,5))-FIND("/",RIGHT(A8,
5))) (which works fine)

Any ideas as to why this isn't working would be greatly appreciated!!

Version: Excel v.2000

TIA,
David

 

[Bob Phillips]

It might be text that is the problem, try

=MATCH(--B8,'pluto ids'!A2:A66,0)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[David]

Well, that worked! Can you please explain what the problem was and
what that did?!?!?

Thanks so much,
David

 

[Bob Phillips]

Yeah sure.

The array that you were looking up was an array of numbers. The lookup up
value was being generated by

=RIGHT(RIGHT(A8,5),LEN(RIGHT(A8,5))-FIND("/",RIGHT(A8,5)))

which returns a text value (even though it looked like a number, RIGHT
returns a string). So you were looking up a text value within an array of
numbers, so it failed.

By putting the double unary before B8 in the lookup formula, --B, I forced
the textual number to a real number, which when looked up matches a value
within the array.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

BTW, you could also have changed the first formula in the same way

=--RIGHT(RIGHT(A8,5),LEN(RIGHT(A8,5))-FIND("/",RIGHT(A8,5)))

and keep the other formula as is.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[David]

Of course! :>

Thanks for the info Bob.

David