Excel formula problem 1

[100dtl]

Hi all,

I have an annoying problem, I have this forumla that looks for a value in another sheet then populates the another column in sheet1,

..this is in sheet1 ...
=VLOOKUP(B2,Sheet2!$A$1:$C$3917,3,FALSE)

I have another formula that looks up in another sheet called stockIn
..
=VLOOKUP(B2,stockIn!$A$2:$C$716,4,FALSE)

I'm not sure why one would work and the other would not, could duplicates cause the problem? the value is in stockIn but the cell is not populated, any help would be excellent

 

[xlbo]

what is the formula returning ??
0 or blank or #N/A ??

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[100dtl]

#N/A

 

[100dtl]

Its done it to a valid cell? #n/a

 

[xlbo]

You've got one of 2 things then

1: Text that looks like numbers
2: Spaces / extraneous characters in one of the datasets

see if any of these 3 work - the one that does will tell us which data error you have

=VLOOKUP(text(B2,"0&quot,stockIn!$A$2:$C$716,4,FALSE)
=VLOOKUP(value(B2),stockIn!$A$2:$C$716,4,FALSE)
=VLOOKUP(trim(B2),stockIn!$A$2:$C$716,4,FALSE)

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[100dtl]

Well done sir/madam... great stuff

text(b2,"0&quot did it/.

 

[xlbo]

What that means is, you have a number in B2
In the dataset you are doing the lookup into, the matching numbers aren't true numbers - they are text that looks like a number

To cleanse your data, enter a 1 into any empty cell
copy it
select the data you want to convert and go
Edit>Paste Special
Tick Values and select multiply

This will convert your "textual numbers" to real numbers and you can dispense with the Text("B2",0)

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244