VLookup with Adjacent Cells

[Sfinally]

I'm trying to create a document in which I can keep a table of Product Numbers with related data in other cells on one tab that will be called up completely in another tab when I input the product number. So in Tab 1 i will have:

<table width="431" border="0" cellpadding="0" cellspacing="0"><col style="width: 86pt;" width="115"> <col style="width: 71pt;" width="94"> <col style="width: 66pt;" width="88"> <col style="width: 101pt;" width="134"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 86pt;" width="115" height="20">Column A</td> <td class="xl65" style="border-left: medium none; width: 71pt;" width="94">Column B</td> <td class="xl65" style="border-left: medium none; width: 66pt;" width="88">Column C</td> <td class="xl65" style="border-left: medium none; width: 101pt;" width="134">Column D</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Product ID</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Product Code</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Product SKU</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Product Description</td> </tr> </tbody></table>
Ideally I want in my second tab to be able to enter a Product ID from Column A and have the three fields to the immediate right fill in with the corresponding data from Columns B through D:

<table width="573" border="0" cellpadding="0" cellspacing="0"><col style="width: 86pt;" width="115"> <col style="width: 71pt;" width="94"> <col style="width: 66pt;" width="88"> <col style="width: 207pt;" width="276"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 86pt;" width="115" height="20">Column A</td> <td class="xl65" style="border-left: medium none; width: 71pt;" width="94">Column B</td> <td class="xl65" style="border-left: medium none; width: 66pt;" width="88">Column C</td> <td class="xl65" style="border-left: medium none; width: 207pt;" width="276">Column D</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Enter Product ID</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Call Up Corresponding Product Code</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Call up Corresponding Product SKU</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Call Up Corresponding Product Description</td> </tr> </tbody></table>
I presume this is a VLookup function but I'm not sure how to bring up the attached data. Any advice on how to do this would be much appreciated. Thanks.

 

[Airfix9]

=VLOOKUP($A1,'Tab 1'!$A:$D,COLUMN(),0)

That should do it, edit to fit your sheets.

 

[Sfinally]

Interesting...the Vlookup formula that I cut and pasted below seems to work, but only pulls up the column to the immediate right of the cell on the other tab, not the entire three.

 

[Airfix9]

You pasted it into each cell to show the results?

 

[Sfinally]

I made an alteration, which probably is screwing me up. The idea here, really, is to be able to have a column in which you enter the Product Number and the VLookup populates the corresponding cells from the other tab. So I'm actually using it like this


<table width="904" border="0" cellpadding="0" cellspacing="0"><col style="width: 131pt;" width="175"> <col style="width: 257pt;" width="342"> <col style="width: 147pt;" width="196"> <col style="width: 143pt;" width="191"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl85" style="height: 15pt; width: 131pt;" width="175" height="20">Column C</td> <td class="xl85" style="border-left: medium none; width: 257pt;" width="342">Column D</td> <td class="xl85" style="border-left: medium none; width: 147pt;" width="196">Column E</td> <td class="xl85" style="border-left: medium none; width: 143pt;" width="191">Column F</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl85" style="height: 15pt; border-top: medium none;" height="20">Header "Product Number"</td> <td class="xl85" style="border-top: medium none; border-left: medium none;">Header "Product Code"</td> <td class="xl85" style="border-top: medium none; border-left: medium none;">Header "Product SKU"</td> <td class="xl85" style="border-top: medium none; border-left: medium none;">Header "Product Description"</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl85" style="height: 15pt; border-top: medium none;" height="20">Blank, awaiting entry</td> <td class="xl85" style="border-top: medium none; border-left: medium none;">Formula "=VLOOKUP($C3,'Tab 1'!$A:$F,COLUMN(),0)"</td> <td class="xl85" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl85" style="height: 15pt; border-top: medium none;" height="20">Blank, awaiting entry</td> <td class="xl85" style="border-top: medium none; border-left: medium none;">Formula "=VLOOKUP($C4,'Tab 1'!$A:$F,COLUMN(),0)"</td> <td class="xl85" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl85" style="height: 15pt; border-top: medium none;" height="20">Blank, awaiting entry</td> <td class="xl85" style="border-top: medium none; border-left: medium none;">Formula "=VLOOKUP($C5,'Tab 1'!$A:$F,COLUMN(),0)"</td> <td class="xl85" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl85" style="height: 15pt; border-top: medium none;" height="20">Blank, awaiting entry</td> <td class="xl85" style="border-top: medium none; border-left: medium none;">Formula "=VLOOKUP($C6,'Tab 1'!$A:$F,COLUMN(),0)"</td> <td class="xl85" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl85" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>
And copy the formula down as many lines as we expect to have entries. The idea is that you type in a Product number in Column C and bring up the relevant other details to populate in D, E, and F.

 

[Airfix9]

Put the formula in the cells in columns E and F then!



As a tip, if you want to show something only once a product number is entered, you can amend your formula thus:

=IF(ISERROR(VLOOKUP($C3,'Tab 1'!$A:$A,1,0)),"Enter Prod No",VLOOKUP($C3,'Tab 1'!$A:$F,COLUMN(),0))

 

[Sfinally]

I'm so close here. The formula works fine, except the results from the reference tab are displayed in the exact position/column in the results. So I want to move everything two columns to the right, but when i do that the resulting data is out of sync. Moving the columns in the reference tab two to the right doesn't resolve either. Is there anything in the formula that asks the data to be displayed in the exact same position/column as it sits in the reference tab? Thanks again for your help.

 

[Airfix9]

The COLUMN() command is a quick way of copying the data across. If you are in column B (2), the reference returned is 2, if C (3) it's 3 etc. You can replace COLUMN() with 2, 3, 4 etc for more stability if you want to move the report across a few columns.