Wild card replace in vlookup

[excelsishya]

Hi all,

I am trying replace column() function used in vlookup with actual number using find replace .But when i do its replacing excel 4 th argument 0 too that away taking to default 1.

input


<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">1</td> <td style="text-align: right;">4</td> <td style="text-align: right;">5</td> <td style="text-align: right;">6</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="text-align: right;">2</td> <td style="text-align: right;">4</td> <td style="text-align: right;">5</td> <td style="text-align: right;">6</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="text-align: right;">3</td> <td style="text-align: right;">4</td> <td style="text-align: right;">5</td> <td style="text-align: right;">6</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>
</td> <td>
</td> <td>
</td> <td>
</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>D6</td> <td>=VLOOKUP($C6,$K$6:$N$8,COLUMN(B1),0)</td></tr> <tr> <td>E6</td> <td>=VLOOKUP($C6,$K$6:$N$8,COLUMN(C1),0)</td></tr> <tr> <td>F6</td> <td>=VLOOKUP($C6,$K$6:$N$8,COLUMN(D1),0)</td></tr> <tr> <td>D7</td> <td>=VLOOKUP($C7,$K$6:$N$8,COLUMN(B2),0)</td></tr> <tr> <td>E7</td> <td>=VLOOKUP($C7,$K$6:$N$8,COLUMN(C2),0)</td></tr> <tr> <td>F7</td> <td>=VLOOKUP($C7,$K$6:$N$8,COLUMN(D2),0)</td></tr> <tr> <td>D8</td> <td>=VLOOKUP($C8,$K$6:$N$8,COLUMN(B3),0)</td></tr> <tr> <td>E8</td> <td>=VLOOKUP($C8,$K$6:$N$8,COLUMN(C3),0)</td></tr> <tr> <td>F8</td> <td>=VLOOKUP($C8,$K$6:$N$8,COLUMN(D3),0)</td></tr></tbody></table></td></tr></tbody></table>

in above i selected column d and tried

ctrl+f equal to COLUMN*

replace with 2

the outcome was show below whereas desired output was VLOOKUP($C6,$K$6:$N$8,2,0),VLOOKUP($C7,$K$6:$N$8,2,0) so on


I am using excel 2007.

Thanks in advance
<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>D</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">4</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="text-align: right;">4</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="text-align: right;">4</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>D6</td> <td>=VLOOKUP($C6,$K$6:$N$8,2)</td></tr> <tr> <td>D7</td> <td>=VLOOKUP($C7,$K$6:$N$8,2)</td></tr> <tr> <td>D8</td> <td>=VLOOKUP($C8,$K$6:$N$8,2)</td></tr></tbody></table></td></tr></tbody></table> <table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>D</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">4</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="text-align: right;">4</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="text-align: right;">4</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>D6</td> <td>=VLOOKUP($C6,$K$6:$N$8,2)</td></tr> <tr> <td>D7</td> <td>=VLOOKUP($C7,$K$6:$N$8,2)</td></tr> <tr> <td>D8</td> <td>=VLOOKUP($C8,$K$6:$N$8,2)</td></tr></tbody></table></td></tr></tbody></table>

 

[jasonb75]

Ctrl + F replace COLUMN(??)

 

[excelsishya]

Jason, thanks it worked.

original vlookup was for 500 rows.
so i replaced particular column 3 times

COLUMN(??) ,COLUMN(???) & COLUMN(????)