vlookup error...

[Rajmahal]

i have 2 tabs.

sheet1 -- contain list of name and id#.
ID# (sorted), Name
101
102
103
1001
1002

sheet 2 -- contain id#
ID#. Col. B
101
1001
102
103
1002

in sheet 2, i want to check if the id# exists in Sheet 1, and if does, i
want it to display the id in col. b.
=VLOOKUP(A1,Sheet1!$1:$100,1)

When, i do this, most of the time, it works, however some time the result
does not come exact.
Like if it was looking for 1002, the result will show as 102 and end.

Formatted all col. as text. what can i do to fix this.

 

[Rajmahal]

PS: sometimes, i have to go back to the col 1 on sheet 2. and "ENTER"
individually to get them to work.

 

[Franz Verga]

Nel post:[email protected],

i have 2 tabs.

sheet1 -- contain list of name and id#.
ID# (sorted), Name
101
102
103
1001
1002

sheet 2 -- contain id#
ID#. Col. B
101
1001
102
103
1002

in sheet 2, i want to check if the id# exists in Sheet 1, and if
does, i want it to display the id in col. b.
=VLOOKUP(A1,Sheet1!$1:$100,1)

When, i do this, most of the time, it works, however some time the
result does not come exact.
Like if it was looking for 1002, the result will show as 102 and end.

Formatted all col. as text. what can i do to fix this.

try this way:

=VLOOKUP(A1,Sheet1!$1:$100,1,0)

or also:

=VLOOKUP(A1,Sheet1!$1:$100,1,false)



--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy

 

[Gord Dibben]

Make sure Tools>Options>Calculation is set to Automatic.


Gord Dibben MS Excel MVP