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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
C

cpugh

New Member
Joined
Jun 30, 2002
Messages
21
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
Peter_SSs

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
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

2
  • Solved
A little help with looping please
2
Replies
10
Views
718
GWteB
GWteB
M
  • Question
Drop down select
Replies
1
Views
1K
madu
M
C
  • Question
Request for a VBA code to login
Replies
7
Views
2K
Rijnsent
Rijnsent
B
  • Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
1K
Peter_SSs
Peter_SSs
D
  • Question
How to output a dynamic range where the height of the range varies
Replies
0
Views
808
dagda13
D

Forum statistics

Threads
1,191,196
Messages
5,985,230
Members
439,950
Latest member
Xearo96

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
Top