Vezerid - Thanks, sorry for my slow response, I have been puzzling over this
problem trying to figure it out. I have VLOOKUP and HLOOKUP formulas in this
spreadsheet and would like to figure out what is happening and keep them
rather than using a different formula. I used the "ISNUMBER" function to
check the reference cells, and the cells are values. I used "LEN" and they
have the same number of characters. One thing I did learn is that the
"HLOOKUP" function that is giving me N/A returns a value when I change the
range lookup to "TRUE" from "FALSE". Unfortunately, the value it is
returning is the one under the one I am looking for. The value I am looking
for, and is referenced in the formula, is 7.5%. It is returning a value
referenced from 7.25%, the closest value that is not greater than the one I
want. That indicates to me that the formula is seeing the 7.5% value in the
table as being greater than 7.5%. It is a number, and it looks like exactly
7.50%. The 7.50% is coming from a formula which is linked to an input cell
of 7.00%, and then it adds 0.0025 to create a range of percentages. The
formula has been working fine in the past, but now it seems to not recognize
the referenced value as exactly 7.50%.

I would greatly appreciate any insight or help. Thank you very much.

Terry McCoy



"vezerid" wrote:

Terry,

Try the following:

=MATCH(7.50%,J54:N54,0)
=MATCH(D44,J54:N54,0)

=MATCH(5,J54:J64,0)
=MATCH(F6+1,J54:J64,0)

=INDEX($J$54:$N$64,MATCH($D44,$J$54:$N$54,0),MATCH (F6+1,$J$54:$J
$64,0))

Does this give you any clues?

HTH
Kostis Vezerides


On Jun 19, 4:15 pm, Terry McCoy
wrote:
Niek - Thanks for replying. The formula is:
=HLOOKUP($D44,$J$54:$N$64,F6+1,FALSE)

The input values in the table a 7.5% for the horizontal value, which is
an investment or capitalization rate, and 5 for the vertical value, which is
5 years. The table cycles the investment rate and time period through an
investment formula. The values in the table seem to be correct, and they
change when I change the input variables in the table. The HLOOKUP formula
just does not seem to recognize them as values. The table is listed below:

7.00% 7.25% 7.50% 7.75% 8.00%
1 $141.04 $136.18 $131.64 $127.39 $123.41
2 $176.95 $170.85 $165.15 $159.82 $154.83
3 $184.56 $178.20 $172.26 $166.70 $161.49
4 $191.55 $184.95 $178.78 $173.02 $167.61
5 $194.41 $187.70 $181.45 $175.59 $170.11
6 $203.93 $196.90 $190.34 $184.20 $178.44
7 $209.93 $202.69 $195.94 $189.61 $183.69
8 $219.27 $211.70 $204.65 $198.05 $191.86
9 $225.80 $218.01 $210.74 $203.94 $197.57
10 $232.86 $224.83 $217.33 $210.32 $203.75

This spreadsheet was originally in Excel 2003 and I converted it to Excel
2007. Could that have anything to do with it? I am having what seems to be
the same problem in another worksheet in this same workbook. I created this
workbook by doing a "Save As" of another Workbook. Since the problem seems
to be in more than just one place, I wonder if it might have to do with the
"Save As"?.

Thank you very much, I would appreciate any help.

Terry McCoy.

"Niek Otten" wrote:
Hi Terry,

Post your formula, input vales and relevant table values. Is the table sorted?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Terry McCoy" wrote in ...
| Folks - I am having a problem with a workbook in Excel 2007. I have what
| appear to be values, - numbers - in cells, but formulas are returning an #N/A
| error that the value is not available. I am getting the error from an
| HLOOKUP formula in one spreadsheet, and I was also getting it from another
| spreadsheet in the same workbook when I tried to use the average function and
| the array function. I used the "isnumber" function, and they are numbers.
| The HLOOKUP formula is referring to date in a table. It was working fine
| before, but now I am getting this error. This workbook is from another one
| that I "Saved As" and then changed the input. The formulas seem to be fine
| in the table. I have not been able to figure it out and would appreciate any
| insight.
|
| Thanks for your help.
|
| Terry McCoy