Match & Index won't reconise certain numbers
 

Hi,

Can anyone help again.

I have set up a spreadsheet with Match and Index to lookup account
numbers, some are just normal numbers and other a prefixed with a
letter before the.

i.e. 10001230 or E10011

match and index doesn't recognise account numbers a that are true
numbers, only the one that start with a letter, i have been using the
below formula, can anyone help?

=INDEX(Account!$A$1:$B$2035,MATCH($A5,Account!$A$1 :$A$2035,0),2)

Regards

Linexe

Match & Index won't reconise certain numbers
 

Hi Linexe,

You are probably comparing text (which may look like numbers) to numbers. You can check with the ISTEXT() or ISNUMBER() function.
You can either change to number using the VALUE() function or change to text using the TEXT() function,, but make sure both are
the same.
Just reformatting doesn't help.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message oups.com...
| Hi,
|
| Can anyone help again.
|
| I have set up a spreadsheet with Match and Index to lookup account
| numbers, some are just normal numbers and other a prefixed with a
| letter before the.
|
| i.e. 10001230 or E10011
|
| match and index doesn't recognise account numbers a that are true
| numbers, only the one that start with a letter, i have been using the
| below formula, can anyone help?
|
| =INDEX(Account!$A$1:$B$2035,MATCH($A5,Account!$A$1 :$A$2035,0),2)
|
| Regards
|
| Linexe
|

Match & Index won't reconise certain numbers
 

Perhaps try it as either:

=INDEX(Account!$A$1:$B$2035,MATCH($A5&"",Account!$ A$1:$A$2035,0),2)

or

=INDEX(Account!$A$1:$B$2035,MATCH(TEXT($A5,"000000 00"),Account!$A$1:$A$2035,0),2)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
Hi,

Can anyone help again.

I have set up a spreadsheet with Match and Index to lookup account
numbers, some are just normal numbers and other a prefixed with a
letter before the.

i.e. 10001230 or E10011

match and index doesn't recognise account numbers a that are true
numbers, only the one that start with a letter, i have been using the
below formula, can anyone help?

=INDEX(Account!$A$1:$B$2035,MATCH($A5,Account!$A$1 :$A$2035,0),2)

Regards

Linexe