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
row 1 will have headers

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
1
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?

Thanks again in advance

David
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 25, 2011 at 07:38 PM
In vlookup the first argument should be the FIRST column. in case this is not first columns dont use vlookup but use index-match.
0