#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 ?

 

[dilyanyordanov]

https://postimg.org/image/8rzn0f4u9/

 

[steve the fish]

Excel isn't seeing at least one of those numbers as a number and it cant coerce them into a number either. See if this produces a result

=CLEAN(TRIM(SUBSTITUTE(E20,CHAR(160),"")))*CLEAN(TRIM(SUBSTITUTE(G20,CHAR(160),"")))

 

[RoryA]

What do the following formulas return:
=ISNUMBER(E20)
=ISNUMBER(G20)

 

[dilyanyordanov]

Excel isn't seeing at least one of those numbers as a number and it cant coerce them into a number either. See if this produces a result

=CLEAN(TRIM(SUBSTITUTE(E20,CHAR(160),"")))*CLEAN(TRIM(SUBSTITUTE(G20,CHAR(160),"")))

Nope...still#value

 

[dilyanyordanov]

What do the following formulas return:
=ISNUMBER(E20)
=ISNUMBER(G20)

If I paste them to 2 empty cells..I get True for E20 and False for G20.....so ?

Hm...if i paste them to another sheets which I have no problem with...Both cells return True...so the problem is with G20 ?

 

[Crazy billy]

relax..

#value

reason solution
No match found = check your table, check your spelling.. etc


post your formula and table on the site so we could study it

 

[Crazy billy]

to avoid it used iferror stament

 

[steve the fish]

Billy. That's crazy. No match found will produce an #N/A! error not a #VALUE! error!

 

[dilyanyordanov]

https://s12.postimg.org/x94qar8t9/excel1.png

check that...you can see the formulas...but...it works for cells H25 and H26....but doesn't for the 4 cells above H25....and G23 doesn't work too..

 

[RoryA]

so the problem is with G20 ?

Yes. You need to work out what exactly is in the cell that the VLOOKUP is returning data from. It's clearly not a real number.