#VALUE! Error while using VLOOKUP()

[rajeshgajipara]

Hi,
I am getting #VALUE Error when i used VLOOKUP function in EXCEL.
I Excel sheet from java application using jxls.
after generating excel sheet by java, i open sheet in excel and i get #VALUE! error
i want values in J column. values are based on condition.
if H column's date matches in range A21:A500 then it returns value from BX (where X is match row no.)
J column shows #VALUE Error.
If i press F2- Save then it shows currect value.
Plese suggest solution.
File have been attched with thread.

[oldchippy]

Hi,

Does this work for you?

=IF(VLOOKUP(H2,$A$21:$B$653,2)="","",VLOOKUP(H2,$A$21:$B$653,2))

[rajeshgajipara]

Dear Sir,

As per your suggestions i tried

=IF(VLOOKUP(H2,$A$21:$B$653,2)="","",VLOOKUP(H2,$A$21:$B$653,2))

but. this gives same #VALUE! Error.
If i update cell without any chenge then it shows proper value.
Above changes works fine in current sheet , but doesn't works in sheet generated by java.
Note: I used excel sheet as a template which contains VLOOKUP function and etc. upto row no.20.

From row no. 21 onwords my data comes from database.

When i write data in excel template and then save and then open, it gives #VALUE! Error.

[Shijesh Kumar]

try this


=IF(ISERROR(VLOOKUP(H2,$A$21:$B$653,2)),"",VLOOKUP(H2,$A$21:$B$653,2))

[rajeshgajipara]

Dear Sir,
Still getting #VALUE! Error.

[ratcat]

G'day,

Try this,

=IF(ISERR(VLOOKUP(H2,$A$21:$B$653,2)),"",VLOOKUP(H2,$A$21:$B$653,2))