I have used VLOOKUP but in few cells it is giving me #N/A in result. I have
noticed that if i change value in a cell from e.g. 111533 to '111533 it
solves the problem but i have more than 800 cells to change.
Is there an easy way to change the format?
Thanks

Would it be easier to change the data in the VLOOKUP table.
Otherwise change the first cell in your table and then use format painter to
change the rest.

HTH
Michael M

"Kashif" wrote:

I have used VLOOKUP but in few cells it is giving me #N/A in result. I have
noticed that if i change value in a cell from e.g. 111533 to '111533 it
solves the problem but i have more than 800 cells to change.
Is there an easy way to change the format?
Thanks

I have used VLOOKUP but in few cells it is giving me #N/A in result. I have
noticed that if i change value in a cell from e.g. 111533 to '111533 it
solves the problem


your matrix has numbers in format of text. the value you search is a number
= N/A, if you change it to a text with ' = it works. that's normal.

you should have the same format on both sides, you must have the same format
in the matrix (you have text). so, you have to change the lookup value from
number to text with text(a1,"#"), like

=vlookup(text(a1,"#"), matrix, column, false)


or you make sure that you matrix contains numbers (change data import,
sql-statement, whatever)

arno

Thanks Michael,
i have tried doing that but it doesn't work.

"Michael M" wrote:

Would it be easier to change the data in the VLOOKUP table.
Otherwise change the first cell in your table and then use format painter to
change the rest.

HTH
Michael M

"Kashif" wrote:

I have used VLOOKUP but in few cells it is giving me #N/A in result. I have
noticed that if i change value in a cell from e.g. 111533 to '111533 it
solves the problem but i have more than 800 cells to change.
Is there an easy way to change the format?
Thanks

Thanks Arno,
Would it be possible to give some more detail on this formula PLEASE.

"arno" wrote:

I have used VLOOKUP but in few cells it is giving me #N/A in result. I have
noticed that if i change value in a cell from e.g. 111533 to '111533 it
solves the problem


your matrix has numbers in format of text. the value you search is a number
= N/A, if you change it to a text with ' = it works. that's normal.

you should have the same format on both sides, you must have the same format
in the matrix (you have text). so, you have to change the lookup value from
number to text with text(a1,"#"), like

=vlookup(text(a1,"#"), matrix, column, false)


or you make sure that you matrix contains numbers (change data import,
sql-statement, whatever)

arno

Would it be possible to give some more detail on this formula PLEASE.

=vlookup(text(a1,"#"), matrix, column, false)

Pls. read the online help on the functions VLOOKUP and TEXT.

=text(a1,"#") makes '1 (=a text) out of 1 (which is a number)

vlookup searches in the first column your data table (matrix) if it can find
'1 and will return the value of the column you specify. "false" means that
it will search for an EXACT match (otherwise it would return the next best
match what you DO NOT WANT!)

arno