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

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" wrote in message
ps.com...
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

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

Thanks so much,
David

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)



"David" wrote in message
oups.com...
Well, that worked! Can you please explain what the problem was and
what that did?!?!?

Thanks so much,
David

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" wrote in message
oups.com...
Well, that worked! Can you please explain what the problem was and
what that did?!?!?

Thanks so much,
David

Of course! :

Thanks for the info Bob.

David