HLOOKUP =N/A "Value Not Available"

TMcCoy

New Member
Joined
Jun 19, 2008
Messages
5
Folks - My name is Terry McCoy, and this is my first time on this forum. I have been reading the message boards, but this is my first post. I am getting an error message in a spreadsheet, and am hoping someone can illuminate me.

I am using a spreadsheet in Excel 2007. It has worked fine in the past, but I recently did a "Save As" to copy it and input different data. The spreadsheet has an "Hlookup" formula in it, and it is giving me an =N/A error and says the "Value is not available". I hate it when that happens. There are other "Lookup" formulas in the worksheet and they seem to be ok. The "Hlookup" formula refers to a dual variable table. The references all seem to be numbers, not text. The formula has "False" for the range lookup argument, which seems to be why I am getting N/A. I changed it to "True", and the formula returned a value, but it is not the value I want. It is the value directly less than the exact match I am looking for. From what I can tell, the lookup value argument (in this case 7.50%), and the comparison value I am looking for (also 7.50%) are exact matches, but the formula seems to think the comparison value is more than 7.50%. I have no clue why. The comparison values derive from another cell where 7.00% is entered, and then 0.0025 is added to 7.00% to create a range of interest rates. I used the LEN function, and the lookup value argument and comparison values seem to have the same number of characters. I would appreciate any suggestions, tips, comments or whatever.

Thank you very much.

Terry McCoy.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Can you show a sample of how your data's laid out? And the formula you're invoking?

Any lookup that is looking for an exact match will return an error if an exact match doesn't exist within the data you're asking to be checked against.

I'm sure a solution can be found once we see what you're working with.

Matty
 
Upvote 0

TMcCoy

New Member
Joined
Jun 19, 2008
Messages
5
Matty - Thank you for the prompt reply.
The formula is =(HOOKUP($D$44,$J$54:$N$64,F6+1,FALSE))
$D$44 refers to a cell that has the lookup value, in this case 7.50%. The range is the table, which I have pasted below. F6+1 is the reference cell for the row index, in this case, 4. I agree with you, it needs to be an exact match for the formula to pick up 7.50%. I do not understand why it is not an exact match. Thank you for your help.

<TABLE style="WIDTH: 320pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=425 border=0><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" span=5 width=85><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #0066ff" align=right width=85 height=17>7.00%</TD><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #0066ff" align=right width=85>7.25%</TD><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #0066ff" align=right width=85>7.50%</TD><TD class=xl90 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #0066ff" align=right width=85>7.75%</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #0066ff" align=right width=85>8.00%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$141.04</TD><TD class=xl81 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$136.18</TD><TD class=xl81 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$131.64</TD><TD class=xl81 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$127.39</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$123.41</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$176.95</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$170.85</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$165.15</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$159.82</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$154.83</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$184.56</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$178.20</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$172.26</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$166.70</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$161.49</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$191.55</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$184.95</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$178.78</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$173.02</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$167.61</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$194.41</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$187.70</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$181.45</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$175.59</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$170.11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$203.93</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$196.90</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$190.34</TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$184.20</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$178.44</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$209.93</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$202.69</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$195.94</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$189.61</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$183.69</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$219.27</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$211.70</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$204.65</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$198.05</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$191.86</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$225.80</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$218.01</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$210.74</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$203.94</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$197.57</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$232.86</TD><TD class=xl87 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$224.83</TD><TD class=xl87 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$217.33</TD><TD class=xl87 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$210.32</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$203.75</TD></TR></TBODY></TABLE>
 
Upvote 0

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hello TMcCoy,

If you're wanting the pull the value in from Row 4, why wouldn't you just put this in your formula?:

=HLOOKUP($D$44,$J$54:$N$64,5,FALSE)

Is there a value in the Column to the left of 7.00%? An INDEX, MATCH, MATCH formula might be a more robust solutuon. See here for an example of how this works:

http://www.contextures.com/xlFunctions03.html

Matty
 
Last edited:
Upvote 0

TMcCoy

New Member
Joined
Jun 19, 2008
Messages
5
Matty - I tried just putting a value in for the row index, and it did not change the result, I am still getting an N/A error. It is a formula for the row index because the table uses it to cycle through different variables. There is a value to the left of 7.00%, it is the formula for the table. I am all for a more robust formula that will not do this. Thanks.
 
Upvote 0

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
This formula is working well for me:

=HLOOKUP($D$44,$J$54:$N$64,5,FALSE)

Are you sure your lookup value (D44) is formatted exactly the same as the values in J54:N54? If it isn't, the lookup will fail.

If Cell D44 is correctly formatted, perhaps highlight the Cell and then press the Format Painter button, and then drag over Cells J54:N54.

Does the HLOOKUP now work?

Matty
 
Upvote 0

TMcCoy

New Member
Joined
Jun 19, 2008
Messages
5
Matty - The format painter seemed to work. ( A new trick for me, I had never used it before). The spreadsheet does not work the way it was linked before, but it does work if I just plug the percentages in as values. That did not work before I did the format painter. I am not sure why that is, but for now I will take it. Thank you very much for your help. I have been wrestling with this since Wednesday, and this is the first solution I have found.

Do you think it would help make this spreadsheet more stable or robust if I have less links and enter more values by hand? Do you have any general guidelines on things to do or avoid to make a spreadsheet more reliable and less prone to errors?

Thanks again, I appreciate your time and expertise.
 
Upvote 0

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hi TMcCoy,

A couple of things to avoid when using lookup formulas:

1) Ensure no excess spaces exist in your lookup_value or table_array. For example, if an extra space exists at the end of a string, where someone has pressed the space bar after entering the value. If that extra space doesn't exist in both the lookup_value and table_array, the lookup will fail. You can use Excel's TRIM function to get rid of any unwanted spaces.

2) Rounded numbers. For example, 1.11 round to one decimal place is not the same as 1.12 rounded to one decimal place, even though they would both look the same on the Worksheet. If you tried to do a lookup where one of the values was your lookup_value and the other was within your table_array, the lookup would fail.

Those are a couple of the most common problems (well ones I've come across anyway), but I'm sure there are others.

Key message is to ensure the values you're using as lookup_values and the values within your table_array are exactly the same.

Hope this helps.

Matty
 
Upvote 0

TMcCoy

New Member
Joined
Jun 19, 2008
Messages
5
Matty - Thanks, that does help. I appreciate your taking the time and trouble to work with me on this.
 
Upvote 0

Forum statistics

Threads
1,191,025
Messages
5,984,198
Members
439,877
Latest member
kellylet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top