Vlookup not seelecting the right column

gwildgoose

New Member
Joined
Nov 2, 2011
Messages
5
I am currently using Version 14.0.5128.5 (32-bit) MS Excel. I have created a spreadsheet with the following formula:

=VLOOKUP(C12&D12,'Flange Dimensions'!$A$6:$E$37,3) very basic

C12 is a letter designation (a or p) and D12 is a number designation (.5 - 14)

The problem I am having is the table contains items A.5 thru A14. Vlookup is confusing A4 for A14 in the table. In other words, The value for a specific column in the table for A14 is showing up when I am requesting that value for A4.

the table 'Flange Dimensions' is set up as follows

<table border="0" cellpadding="0" cellspacing="0" width="320"><col style="width:48pt" width="64"> <col style="width:48pt" span="4" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">Flange</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl64" style="height:15.75pt" height="21"> </td> <td class="xl64">Dia</td> <td class="xl64">Thk</td> <td class="xl64">Bolt Dia</td> <td class="xl64"># Bolts</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A.5</td> <td class="xl63">0.5</td> <td class="xl63">0.4375</td> <td class="xl63">0.625</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A.75</td> <td class="xl63">0.75</td> <td class="xl63">0.5</td> <td class="xl63">0.625</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A1</td> <td class="xl63">1</td> <td class="xl63">0.5625</td> <td class="xl63">0.625</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A1.25</td> <td class="xl63">1.25</td> <td class="xl63">0.625</td> <td class="xl63">0.625</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A1.5</td> <td class="xl63">1.5</td> <td class="xl63">0.6825</td> <td class="xl63">0.625</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A2</td> <td class="xl63">2</td> <td class="xl63">0.75</td> <td class="xl63">0.75</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A2.5</td> <td class="xl63">2.5</td> <td class="xl63">0.875</td> <td class="xl63">0.75</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A3</td> <td class="xl63">3</td> <td class="xl63">0.9375</td> <td class="xl63">0.75</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A3.5</td> <td class="xl63">3.5</td> <td class="xl63">0.9375</td> <td class="xl63">0.75</td> <td class="xl63">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A4</td> <td class="xl63">4</td> <td class="xl63">0.9375</td> <td class="xl63">0.75</td> <td class="xl63">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A5</td> <td class="xl63">5</td> <td class="xl63">0.9375</td> <td class="xl63">0.75</td> <td class="xl63">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A6</td> <td class="xl63">6</td> <td class="xl63">1</td> <td class="xl63">0.875</td> <td class="xl63">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A8</td> <td class="xl63">8</td> <td class="xl63">1.125</td> <td class="xl63">0.875</td> <td class="xl63">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A10</td> <td class="xl63">10</td> <td class="xl63">1.1875</td> <td class="xl63">0.875</td> <td class="xl63">12</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A12</td> <td class="xl63">12</td> <td class="xl63">1.25</td> <td class="xl63">1</td> <td class="xl63">12</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">A14</td> <td class="xl63">14</td> <td class="xl63">1.25</td> <td class="xl63">1</td> <td class="xl63">12</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P.5</td> <td class="xl63">0.5</td> <td class="xl63">0.375</td> <td class="xl63">0.625</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P.75</td> <td class="xl63">0.75</td> <td class="xl63">0.375</td> <td class="xl63">0.625</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P1</td> <td class="xl63">1</td> <td class="xl63">0.375</td> <td class="xl63">0.625</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P1.25</td> <td class="xl63">1.25</td> <td class="xl63">0.5</td> <td class="xl63">0.625</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P1.5</td> <td class="xl63">1.5</td> <td class="xl63">0.5</td> <td class="xl63">0.625</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P2</td> <td class="xl63">2</td> <td class="xl63">0.5</td> <td class="xl63">0.75</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P2.5</td> <td class="xl63">2.5</td> <td class="xl63">0.5</td> <td class="xl63">0.75</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P3</td> <td class="xl63">3</td> <td class="xl63">0.5</td> <td class="xl63">0.75</td> <td class="xl63">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P3.5</td> <td class="xl63">3.5</td> <td class="xl63">0.5</td> <td class="xl63">0.75</td> <td class="xl63">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P4</td> <td class="xl63">4</td> <td class="xl63">0.5</td> <td class="xl63">0.75</td> <td class="xl63">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P5</td> <td class="xl63">5</td> <td class="xl63">0.5</td> <td class="xl63">0.75</td> <td class="xl63">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P6</td> <td class="xl63">6</td> <td class="xl63">0.5</td> <td class="xl63">0.875</td> <td class="xl63">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P8</td> <td class="xl63">8</td> <td class="xl63">0.75</td> <td class="xl63">0.875</td> <td class="xl63">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P10</td> <td class="xl63">10</td> <td class="xl63">0.75</td> <td class="xl63">0.875</td> <td class="xl63">12</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P12</td> <td class="xl63">12</td> <td class="xl63">0.75</td> <td class="xl63">1</td> <td class="xl63">12</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">P14</td> <td class="xl63">14</td> <td class="xl63">0.75</td> <td class="xl63">1</td> <td class="xl63">12</td> </tr> </tbody></table>

any help would be appreciated
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Change your formula to:

VLOOKUP(C12&D12,'Flange Dimensions'!$A$6:$E$37,3,0)

Should make the vlookup an exact match search.
 
Upvote 0
I just tried that and received a #N/A which I believe is a "value not available" error.

Thanks for having a look though. I am still fighting it.
 
Upvote 0
I found part of the problem. The value in D12 when a decimal like .5 was entered was actually 0.5 which changed the designation for the comparision to the table.

The original problem remains.
 
Upvote 0
Perhaps

VLOOKUP(C12&TEXT(D12,"."&REPT(0,LEN(D12)-FIND(".",D12))),'Flange Dimensions'!$A$6:$E$37,3,0)
 
Upvote 0
Is it possible to change the formatting in cell D12 to text? Also, be sure to keep the exact match 0 in the vlookup formula.
 
Upvote 0
Re: Vlookup not selecting the right column

I suppose I could try any number of things. I was mainly wondering why it is reading the correct column but wrong row.

I want to thank everybody who suggested things. I will let you know the work around.

Even with the ,0 at the end of the statement it still looks for the 14 instead of the 4....just weird
 
Upvote 0
Re: Vlookup not selecting the right column

I suppose I could try any number of things. I was mainly wondering why it is reading the correct column but wrong row.

Because you omitted the 4th argument in the Vlookup.
The 4th argument is a True or False.
True means it looks for closest match -and data must be sorted ascending
False means it looks for an exact match - data doesn't need to be sorted.

When that argument is omitted, it defaults to True

so your formula was looking for a closest match.
Which means your data needs to be sorted Ascending.
Which it is not.


then you tried adding the 0 as the 4th argument
To make it look for an exact match.
That returned N/A meaning there was no exact match

That's when you discovered the difference between D12 and the actual data in the table.
D12 was 0.5 where the table was just .5 - not an exact match..


That pretty much sums up the thread.


did you try my suggestion in post #5 ?
 
Upvote 0
And the winner is Jonmo1! You were dead on about the sorting of the table. As soon as I sorted the table in ascending order, bingo was his name-o!

I thought the table was sorted in ascending order because it went from smallest flange to largest, not even thinking that the spreadsheet read it differently than the way I put it in.

Everyone who answered....Thank You!

JonMo1, take a bow, your the man!

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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