Vlookup not seelecting the right column

[gwildgoose]

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

 

[Asala42]

Change your formula to:

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

Should make the vlookup an exact match search.

 

[gwildgoose]

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.

 

[gwildgoose]

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.

 

[Jonmo1]

ADVERTISEMENT

Perhaps

VLOOKUP(C12&TEXT(D12,"."&REPT(0,LEN(D12)-FIND(".",D12))),'Flange Dimensions'!$A$6:$E$37,3,0)

 

[Jythier]

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.

 

[gwildgoose]

ADVERTISEMENT

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

 

[Jonmo1]

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 ?

 

[gwildgoose]

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