Dear StackExchange community,
https://www.mrexcel.com/forum/excel-questions/552781-vlookup-value-not-available-error.html
https://www.mrexcel.com/forum/about-board/1017050-mrexcel-html-maker-update-4.html
Please assist - I have used the MrExcelHtml formatting utility (linked above) to aid converting an excel sheet to HTML. I have run and tested it in standard browser as a standalone HTML page, and the spreadsheet is displayed beautifully. But when I paste the same HTML into the forum posting editor, the result is not achieved... The part example I have attempted to author below is really tricky to explain without the formatted table - if there is an alternative method or workaround, I'll be happy to employ it next month when I return from holiday.
Thanks in advance,
A
The following Excel 2010 question re the VLOOKUP value not available error has been google searched as per the forum below; https://www.mrexcel.com/forum/excel-questions/552781-vlookup-value-not-available-error.html
Let's pretend we need to lookup by exact value matching, the enumeration of some unique decimal values, some of which might extend to 4 decimal places. In Office 2010 Excel v14, VLOOKUP() seems able to handle up to 4 decimal places and I'd be interested if anyone's had success making it find exact matches for values with more decimal places than this (fair enough; there has to be a limit somewhere!)
The following values are an example subset from 1000's of enumerated values, which must be matched exactly (and only exactly) to backwards derive an enumeration:
A B
110.9788 <t> 1
110.97885 <t> 2
110.9789 <t> 3
...but as you'll notice, A2 has 5dp's...too many for VLOOKUP. Interestingly, in the 'general' cell format view, cell A2
=VLOOKUP(110.9788, A:B, 2, FALSE)
=VLOOKUP(110.97885, A:B, 2, FALSE)
=VLOOKUP(110.9789, A:B, 2, FALSE)
Then run the below in your VBA immediate window...
activesheet.cells(8,2).value = worksheetfunction.RoundUp(activesheet.cells(4,2).value, 4)
<b>Excel 2010</b>
<table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">activesheet.cells(10,2).value = worksheetfunction.RoundUp( activesheet.cells(6,2).value, 4)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">activesheet.cells(11,2).value = worksheetfunction.Floor(activesheet.cells(7,2).value,1)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">activesheet.cells(12,2).value = 0.1 * fix(10 * activesheet.cells(7,2).value)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="font-weight: bold;;">how values were input into B column</td><td style="font-weight: bold;;">values</td><td style="font-weight: bold;;">enum</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">typed values</td><td style="font-weight: bold;;">Vlookup Result</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">typed 110.9775</td><td style="text-align: right;;">110.9775</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">110.9788</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">typed 110.97885</td><td style="text-align: right;;">110.9789</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">110.9789</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">typed 110.979</td><td style="text-align: right;;">110.979</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">110.9789</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">rounddown(B4)</td><td style="text-align: right;;">110.9788</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">110</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">copy pasted from B4</td><td style="text-align: right;;">110.9789</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">110.9</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">using cell A1's VBA code</td><td style="text-align: right;;">110.9789</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style=";">F4=VLOOKUP(E4,B:C,2,FALSE)</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">using cell A2's VBA code</td><td style="text-align: right;;">110</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style=";">F5=VLOOKUP(E5,B:C,2,FALSE)</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">using cell A3's VBA code</td><td style="text-align: right;;">110.9</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style=";">etc..</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">=ROUNDDOWN(<font color="Blue">B6,4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F5</th><td style="text-align:left">=VLOOKUP(<font color="Blue">E5,B:C,2,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">E6,B:C,2,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F7</th><td style="text-align:left">=VLOOKUP(<font color="Blue">E7,B:C,2,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F8</th><td style="text-align:left">=VLOOKUP(<font color="Blue">E8,B:C,2,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">E9,B:C,2,FALSE</font>)</td></tr></tbody></table></td></tr></table><br />