#value on some cells only

[dilyanyordanov]

Hi guys, I am doing a report for work and I used vlookup for some of the cells to go find information from other sheets. The problem is with a particular person's name only... excel gives me #value on specific cells only. Others work fine.... All data type is either general or number. I tried and changed it but nothing... I copied one of the sheets from another person which everything was calculated ok...but still the same problem occurs. Any suggestions ?

 

[AliGW]

Have you checked for leading or trailing spaces?

 

[CsJHUN]

Hi dilyanyordanov!

#value should cause many things, look around here (office support) And/or you have to give us more info.

 

[steve the fish]

Only reason I can think of getting a value error on a VLOOKUP result is if there is a value error in the lookup table.

 

[dilyanyordanov]

What do you mean by value error ? I have the same sheets for different people and on their sheets I don't get any errors.

 

[AliGW]

You said there was a value error! Have you looked for leading or trailing spaces?

 

[RoryA]

The point is that if VLOOKUP cannot find a match for the lookup value, it returns #N/A and not #VALUE. The latter should only be returned if the result cell in the lookup table contains a #VALUE error, or if you are including some other calculation in the VLOOKUP formula.

 

[dilyanyordanov]

Ok, a little more info...The cell that returns #value is "=g20*e20" ..... that's it... and it doesn't work for 3 cells in the column and works for the rest... g20 is a vlookup ...and e20 has an index function.

 

[steve the fish]

What is the value of the two cells in the row where the error is occurring?

 

[dilyanyordanov]

e20 is .... 521.09 ....g20 is 0.66.... Right now they are in "general" type... used to be in "numbers"