Need help with Vlookup

[jackoat]

Hi

I am trying to use Vlookup in the following situation but I an an error
result "#N/A". Basically, I have a table that consists of the following
columns

Col 1: Month number: where e.g. "5" represents month no. 5 etc
Col 2: Principal
Col 3: Interest
Col 4: Ending Balance
Col 5: Cumulative interest
Col 6: paid up to year

For col 6, I use the formula
datedif (loan_start,b16,"Y") + datedif(loan_start,b16,"YM")/100 which
gives me a year month format as yr.mth e.g. (10.01 means 10 yr 1 mth,

20.11 means 20 years 11 mth etc)

Now, on ANOTHER sheet, I have a value, a year "20" that I want Vlookup
to find in the above-mentioned table and return the corresponding
cumulative interest in the 5th column. However, I get an #N/A. Could
there be some inconsistency between my "20" in the first sheet and the
"20.00" as given by the formula of column 6 in the 2nd sheet?

Thanks for your help!!

Jack

 

[Ron Rosenfeld]

Hi

I am trying to use Vlookup in the following situation but I an an error
result "#N/A". Basically, I have a table that consists of the following
columns

Col 1: Month number: where e.g. "5" represents month no. 5 etc
Col 2: Principal
Col 3: Interest
Col 4: Ending Balance
Col 5: Cumulative interest
Col 6: paid up to year

For col 6, I use the formula
datedif (loan_start,b16,"Y") + datedif(loan_start,b16,"YM")/100 which
gives me a year month format as yr.mth e.g. (10.01 means 10 yr 1 mth,

20.11 means 20 years 11 mth etc)

Now, on ANOTHER sheet, I have a value, a year "20" that I want Vlookup
to find in the above-mentioned table and return the corresponding
cumulative interest in the 5th column. However, I get an #N/A. Could
there be some inconsistency between my "20" in the first sheet and the
"20.00" as given by the formula of column 6 in the 2nd sheet?

Thanks for your help!!

Jack

Undoubtedly there is a problem in your VLOOKUP formula. For one thing, (from
HELP) Lookup_value is the value to be found in the FIRST column of the array.

In your description, you are looking for the value in the 6th column and trying
to find the match in the 5th column. You cannot do that with VLOOKUP.

You need to either rearrange your table, so that PdUpToYr is to the left of the
info you are looking for, or use a different approach.

For example, something like:

=INDEX(CumInt,MATCH(H2,PdUpToYr))

where CumInt and PdUpToYr are the cell references for the appropriate columns
might work for you. But read HELP for MATCH so you understand the importance
of the optional argument, and whether that applies in what you want to do.


--ron