Vlookup and DatedIf Problem Value not available

[AmrsDavid]

Hi

I'm having a problem using datedif function with Vlookup.

I have a range of cells where I use datedif to calculate a persons age in years and months (6.11 being 6 years and 11 months)

I am then trying to vlookup a value in a table based on the age calculated from datedif.

I keep getting a value not available error #NA. I think the error is due to way the values are shown for the age in the lookup table but I haven't been able to work out how to show they in same details as datedif is answering.


I have attached a sample spreadsheet, my error is in cells named "normal range", which should show a ranged answer based on the calcuated age.



Help

[alansidman]

Suggest you post a sample workbook.

[AmrsDavid]

Hello Alan

I edited my post and attached a sample spreadsheet.

[FDibbins]

1st, the answers in sheet1 column E are text, so adjust the formula to...=(DATEDIF(D2,$A$2,"y") & "." & DATEDIF(D2,$A$2,"ym"))*1

however, even when that change is made, it still returns an unexplained NA. despite the fact that the data in sheet2 column J ATE numbers (tested with isnumber) only when you manually overwrite the formula with its value, does the vlookup work. i dont understand it. use "fill range" to replace the formula in K with actual values and everything works fine

[vlady]

or also putting the *1 in the lookup and change to "true"

=VLOOKUP(E3*1,Data!$J$3:$K$88,2,TRUE)

note: -> no changes on the different tables

[FDibbins]

@ vlady nope that still doesnt work. if you still have the file open, check the answer on G6 (6.9). using TRUE returns 15-27, but it should be 13-25. makes no sense to me why its not working based on his formula on sheet2 column J?

[vlady]

J4=TEXT(6,"0.00")
then below
J5=TEXT(J4+0.1,"0.00")


E2=TEXT((DATEDIF(D2,$A$2,"y") & "." & DATEDIF(D2,$A$2,"ym")),"0.00")

no change in vlookup
=VLOOKUP(E3,Data!$J$3:$K$88,2,FALSE)