Vlookup error, certain text values not working in lookup.

[sixfoot10]

I am currently working with vlookup and getting some very strange results in Excel 2003.

The bottom row is a a vlookup to find information from a particular month listed above. This is eventually going to be built into a larger formula to show percentage change between 2 months.

The formula I am using in B6 is =vlookup($A$5,$A$2:$G$4,2)
the 2 is changed to 3 and so on for the appropriate columns

As you can see when I use the value of Jan 10 I have all 0's returned. If I chnage Jan 10 in F1 to Dec 09 or Feb 10 I get the values for the appropriate rows.

To add to the confusion If I change Jan 10 in the table and the lookup value to Jan or January it still does not work. I then tried a few random values in there and found the following. Der 10, duncan and egg work while book, march, josh and try do not.

The cells in column A are all set to Text.

The only changes I am making for my tests were substituting the values in column A. I also tried copying Jan 10 and using paste value but this produced the same results.

Any ideas?

 

[bprasad]

I am not sure what you are looking for but with given data, the vlookup is doing fine for me in Excel 03. Pl see attached:

<title>Excel Jeanie HTML</title>Sheet4

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Garamond,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 62px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 56px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>Retailer</td> <td>header</td> <td>header</td> <td>header</td> <td>header</td> <td>header</td> <td>header</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: right;">Dec-09</td> <td style="text-align: right;">0</td> <td style="text-align: right;">35</td> <td style="text-align: right;">248</td> <td style="text-align: right;">43</td> <td style="text-align: right;">7</td> <td style="text-align: right;">317</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">Jan-10</td> <td style="text-align: right;">0</td> <td style="text-align: right;">10</td> <td style="text-align: right;">276</td> <td style="text-align: right;">37</td> <td style="text-align: right;">1</td> <td style="text-align: right;">305</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">Feb-10</td> <td style="text-align: right;">0</td> <td style="text-align: right;">16</td> <td style="text-align: right;">178</td> <td style="text-align: right;">28</td> <td style="text-align: right;">2</td> <td style="text-align: right;">136</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">Jan-10</td> <td style="text-align: right;">0</td> <td style="text-align: right;">10</td> <td style="text-align: right;">276</td> <td style="text-align: right;">37</td> <td style="text-align: right;">1</td> <td style="text-align: right;">305</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B6</td> <td>=VLOOKUP($A$6,$A$2:$G$4,COLUMN(),0)</td></tr> <tr> <td>C6</td> <td>=VLOOKUP($A$6,$A$2:$G$4,COLUMN(),0)</td></tr> <tr> <td>D6</td> <td>=VLOOKUP($A$6,$A$2:$G$4,COLUMN(),0)</td></tr> <tr> <td>E6</td> <td>=VLOOKUP($A$6,$A$2:$G$4,COLUMN(),0)</td></tr> <tr> <td>F6</td> <td>=VLOOKUP($A$6,$A$2:$G$4,COLUMN(),0)</td></tr> <tr> <td>G6</td> <td>=VLOOKUP($A$6,$A$2:$G$4,COLUMN(),0)</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

BTW, I dont understand reference of A5 as lookup value in your formula.

 

[Aladin Akyurek]

Switch to match-type 0 (FALSE)...

Either:

=VLOOKUP($A$5,$A$2:$G$4,2,0)

Or:

=INDEX(B$2:B$4,MATCH($A5,$A$2:$A$4,0))