Vlookup function in excel 2

[terminallyNeurotic]

I am using a Vlookup function in excel to match ID numbers to names. I've about 600 records in the file I want to fill and 3000 records in the file I am looking up for the names.

My problem is that although I have sorted the table I am looking up in ascending order and have put the formula in correctly, I am only getting matching names for the ID numbers that begin with '0' (zero) and #N/A results for the names of the other ID numbers which begin in '1', '2', '3' and '4'. I don't see how I can't get any results returned for ID numbers that begin with any other digit apart from '0'. Do you know if it's something I've done/not done?

All suggestions appreciated

 

[Hasit]

Surprised at the error. Can you supply the formula you are using?

I suspect you are using quotes around the numbers which lead with a zero, i.e. the formula is something like:

=VLOOKUP("01",$A$1:$B$5,2,FALSE)

Would suggest trying the following:

1. Either remove the quotes from IDs that do not start with zero, or;
2. Reformat the numbers starting with zero (suspect the cell looks like '01 etc), to be a single number

 

[xlbo]

I'm guessing that the data is from different sources and the data starting with a 0 is actually text whereas the others are numbers. This would imply that the data you are looking up INTO is text also - to test this, use
=ISNUMBER(A1) or =ISTEXT(A1) where the cell you are testiing is in A1
Basically - both sets of data need to be matching - either all text or all numbers
Suggestion:
On your 600 data set, enter this formula at the right hand side
=TEXT(A1,"0&quot
where your dataset starts in A1
and copy down - then copy and paste special values over your original 600 - betcha it works then Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Hasit]

Geoff, as always, a clear set of instructions and a solution.

Just to confirm, did you mean =TEXT(A1,"00&quot, rather than just one "0"?

 

[xlbo]

errrrrrmmmmm - nope - AFAIK all my text formula does is change a number to a zero decimal textual representation of a number....
Am I missing something in terms of the functionality of the TEXT function ??

Surely this would only make a difference if the number was single digit only as this would create a 01 instead of 1.... Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[terminallyNeurotic]

Thanks for your suggestions Hasit and Geoff. The data starting with 0 is text. The data comes from Unix output which I have reran in excel. I had to changed the cells with the numbers beginning in 0 to text as so as I could get the zero at the start of the number. If the numbers beginning in zero work with the cells formatted as text, how do the other numbers return null results?

The numbers I am matching are about 7 digits long and I would rather not muck around with the formulae incase something gets mixed up!

My formula looks something like this =vlookup(A1,otherSheet$A$1:$B$5,2,FALSE)

 

[xlbo]

Because the other numbers may be FORMATTED as text but they are ACTUALLY numbers - think of formatting as a mask - it just shows you a version of what is actually there. use =ISTEXT(rangeref) on one of your non zero starting numbers that are formatted as text)
I'm willing to bet you get a FALSE answer which means that they are actually numbers
I'd suggest that you are making a very large rod for your own back by having different data types in what appears to be a database like sheet. You should have either ALL text or ALL numbers.
For your current situation, this should work
=if(istext(A1),vlookup(A1,otherSheet$A$1:$B$5,2,FALSE),vlookup(text(A1,"0&quot,otherSheet$A$1:$B$5,2,FALSE))

Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Hasit]

Geoff, Sorry to not have responded earlier. My question was raised because if a leading zero is being searched for, then the single "0" in the TEXT function would strip off the leading zero for "01", and give you "1" instead "01" (in text format).

So in the case above, you may need to put in seven zeros in the TEXT function to ensure that the leading zero is not stripped off.

 

[xlbo]

Hasit - correct - I was confused 'cos you put "00" rather than "0000000"
the reason that I used "0" rather than "0000000" was because I was intending it to be used only on the cells that didn't start with a zero and therefore had to be converted to text but good pickup
Personally, I think that this is a particularly messed up set of data - leading zeros, numbers AND text
It would be much easier if everything was converted to a 7 digit number - 0000000 - with or without leading zeros

Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur