Vlookup

F

Fishbone

I have used VLOOKUP many a time before, but im stumped...

the formula Im using is =VLOOKUP(A2, Cat!A12:D12, 4, 0)

What I want excel to do is search for someones ID no (which is what i
displayed in A2) in the Cat sheet and return what is being displayed i
the fourth column. Everytime I do this though im getting N/A

Now sheet 1 and sheet 2 do not have all the same people in it...Woul
this make a difference

I.e

Sheet One

ID Surname Initials
13 Roberts T
48 Evans L
78 Smith A
174 Price J
494 Jones T


ID Surname Initials Cat
78 Smith A A
494 Jones T
 
V

Vaughan

Vlookup returns #N/A when you are asking for an exact match (as here) and there isn't one. This means the most likely reason for your problem is that you are either looking up a value that is not in the table, or that there is some subtel difference between the ID numbers that is not immediately obvious. For example, they could be text on one sheet and numbers on the other, they could be text on both sheets, but be padded with spaces on one and not the other (these are both problems I have had in the past). You need to inspect your data a bit to pin the reason down.
 
F

Fishbone

Thanks,

That does seem to be the problem. I have tried formating the cells s
that they are both numbers etc... but still cant get it to work. I
there a way of 'defaulting' the cells to set a set format so that the
are both in sync??

Much appreciated

Laurenc
 
D

Dave Peterson

I think that this will make the value text the next time you change that cell.
Just changing the format of the cell to text will still leave the value a
number.
 
D

Dave Peterson

One way to change "text" numbers to "number" numbers.

Select an empty cell
edit|copy
select your "text" numbers range
Edit|Paste special|click the Add button.

If I were concerned, I'd do this to both lists.

===
other things that can cause trouble:
You could have leading/trailing spaces in one list--but not the other, too.
Your values look like 78 on the worksheet, but it's really 78.000000001.

Which won't match 78.
 
Top