Match & Index won't reconise certain numbers

L

lindsey1975

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
 
N

Niek Otten

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

| 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
|
 
M

Max

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,"00000000"),Account!$A$1:$A$2035,0),2)
 
Top