vlookup problem

C

cpugh

New Member
Joined
Jun 30, 2002
Messages
21
<html xmlns:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
xmlns:x="urn:schemas-microsoft-com:eek:ffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="Book1_files/filelist.xml">
<link rel=Edit-Time-Data href="Book1_files/editdata.mso">
<link rel=OLE-Object-Data href="Book1_files/oledata.mso">
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:Author>cait</o:Author>
<o:LastAuthor>cait</o:LastAuthor>
<o:Created>2008-06-22T13:08:47Z</o:Created>
<o:LastSaved>2008-06-23T00:46:06Z</o:LastSaved>
<o:Version>11.5606</o:Version>
</o:DocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:0;
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
font-weight:700;
font-family:Arial, sans-serif;
mso-font-charset:0;
mso-number-format:"\@";
border:.5pt solid windowtext;}
.xl25
{mso-style-parent:style0;
font-weight:700;
font-family:Arial, sans-serif;
mso-font-charset:0;
border-top:.5pt solid windowtext;
border-right:.5pt solid windowtext;
border-bottom:.5pt solid windowtext;
border-left:none;}
.xl26
{mso-style-parent:style0;
mso-number-format:"\@";
border-top:none;
border-right:.5pt solid windowtext;
border-bottom:.5pt solid windowtext;
border-left:.5pt solid windowtext;}
.xl27
{mso-style-parent:style0;
mso-number-format:"\@";
border-top:none;
border-right:.5pt solid windowtext;
border-bottom:.5pt solid windowtext;
border-left:none;}
.xl28
{mso-style-parent:style0;
border-top:none;
border-right:.5pt solid windowtext;
border-bottom:.5pt solid windowtext;
border-left:none;}
.xl29
{mso-style-parent:style0;
border-top:none;
border-right:.5pt solid windowtext;
border-bottom:.5pt solid windowtext;
border-left:.5pt solid windowtext;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Roster</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
<x:HorizontalResolution>600</x:HorizontalResolution>
<x:VerticalResolution>0</x:VerticalResolution>
</x:Print>
<x:Selected/>
<x:TopRowVisible>3</x:TopRowVisible>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
<x:ActiveRow>24</x:ActiveRow>
<x:ActiveCol>1</x:ActiveCol>
<x:RangeSelection>$A$4:$B$25</x:RangeSelection>
</x:Pane>
</x:Panes>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>9300</x:WindowHeight>
<x:WindowWidth>15135</x:WindowWidth>
<x:WindowTopX>480</x:WindowTopX>
<x:WindowTopY>75</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<body link=blue vlink=purple>

<table x:str border=0 cellpadding=0 cellspacing=0 width=170 style='border-collapse:
collapse;table-layout:fixed;width:128pt'>
<col width=64 style='width:48pt'>
<col width=106 style='mso-width-source:userset;mso-width-alt:3876;width:80pt'>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl24 width=64 style='height:12.75pt;width:48pt'>Name</td>
<td class=xl25 width=106 style='width:80pt'>DAY</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah</td>
<td class=xl27>CTL</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah1</td>
<td class=xl27>CTE</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah2</td>
<td class=xl27>11</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah3</td>
<td class=xl27 x:str="'2-10">2-10</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'> </td>
<td class=xl27> </td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'> </td>
<td class=xl27> </td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah5</td>
<td class=xl27>CTL</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah6</td>
<td class=xl27>Th</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah7</td>
<td class=xl27>1/6</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah8</td>
<td class=xl27>1/6</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah9</td>
<td class=xl27>ADO</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah10</td>
<td class=xl27>RDO</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah11</td>
<td class=xl27>RDO</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'> </td>
<td class=xl27> </td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah44</td>
<td class=xl27>3-11</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah33</td>
<td class=xl27>4-12</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'>Blah11</td>
<td class=xl27>11-7</td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl26 style='height:12.75pt'> </td>
<td class=xl28> </td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl29 style='height:12.75pt'> </td>
<td class=xl28> </td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl29 style='height:12.75pt'> </td>
<td class=xl28> </td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl29 style='height:12.75pt'> </td>
<td class=xl28> </td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 class=xl29 style='height:12.75pt'> </td>
<td class=xl28> </td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 colspan=2 style='height:12.75pt;mso-ignore:colspan'></td>
</tr>
<tr height=17 style='height:12.75pt'>
<td height=17 style='height:12.75pt'>Find 2-10</td>
<td x:fmla="=LOOKUP("2-10",$B$2:$B$18,$A$2:$A$18)">Blah8</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
<td width=106 style='width:80pt'></td>
</tr>
<![endif]>
</table>

</body>

</html>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
sorry, i was trying to show my worksheet... not exactly the result I was after.
My problem is that using this formula:LOOKUP("2-10",$B$2:$B$18,$A$2:$A$18) with all cell formatting in column A and column B set as text, I get the wrong answer
 
Upvote 0
cpugh said:
sorry, i was trying to show my worksheet... not exactly the result I was after.
My problem is that using this formula:LOOKUP("2-10",$B$2:$B$18,$A$2:$A$18) with all cell formatting in column A and column B set as text, I get the wrong answer
Click to expand...
The wrong answer is more likely resulting from the fact that the values in your lookup vector (column B) need to be in ascending order for lookup to work properly.

Try INDEX/MATCH instead:

Excel Workbook
ABCDE
1NameDAYDAY2-10
2BlahCTLNameBlah3
3Blah1CTE
4Blah211
5Blah32-10
6
7
8Blah5CTL
9Blah6Th
10Blah71/6
11Blah81/6
12Blah9ADO
13Blah10RDO
14Blah11RDO
15
16Blah443-11
17Blah334-12
18Blah1111-7
19
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
VBA for Web Scraping help
Replies
0
Views
579
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
343
esben
E
A
  • Locked
  • Question
values across tables found by name
Replies
2
Views
374
Fluff
Fluff

Forum statistics

Threads
1,214,523
Messages
6,120,047
Members
448,940
Latest member
mdusw

Share this page

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
Back
Top