# Trouble with VLOOKUP

Closed
capitalscarfie - Jan 23, 2011 at 07:18 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 25, 2011 at 07:38 PM
Hello,

I have two worksheets. The first contains a series of Divisions, Regions and Areas

Division Region Area
Jonestown Alpine West
Springfield Urban East
Fakeplace Inner East
Excelville North West

In the second worksheet, I want to enter the division name (from a drop down list) and have the respective Region and Area appear in the adjacent cells.

I've tried a VLOOKUP but I'm not sure if this is the right way to go?

Some assistance with the code would be hugely appreciated.

## 3 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 24, 2011 at 10:47 PM
main data in sheet 1.

suppose in sheet 2 from A2 down the division names are entered using a drop down list

in sheet 2 cell B2 copy this formula

=VLOOKUP(\$A2,Sheet1!\$A\$1:\$C\$5,COLUMN(B2),0)

now copy B2 to C2

highlight B2 and C2
you can copy down.

if more data is available in sheet 1 then C5 in the second argument of the formula may be changed suitably for e.g. C100
Hi Venkat1926

This worked perfectly. If you're able, can you suggest the reason why when I add some columns to the left of those already on sheet 2, the formula then comes up with a #REF! error?

The formula in the new location (B2 is now I2) is:
=VLOOKUP(\$H2,Sheet1!\$A\$1:\$C\$74,COLUMN(I2),0)

I've made no other changes except to include all 74 rows of data in the formula. I've made no changes to Sheet 1

I can't explain why this won't work?

Can you help?