Trouble with VLOOKUP
Closed
capitalscarfie
-
Jan 23, 2011 at 07:18 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 25, 2011 at 07:38 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 25, 2011 at 07:38 PM
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 24, 2011 at 10:47 PM
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
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
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 25, 2011 at 07:38 PM
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.
