Trouble with VLOOKUP
Closed
capitalscarfie
-
23 Jan 2011 à 19:18
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 25 Jan 2011 à 19:38
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 25 Jan 2011 à 19:38
Related:
- Trouble with VLOOKUP
- Iserror function in excel with vlookup - Guide
- Trouble with netflix - Guide
- Transfer data from one excel worksheet to another automatically vlookup - Guide
- Vlookup compare two columns and return a third ✓ - Excel Forum
- Copying Excel data from one sheet to another based upon contents of a cell - Excel Forum
3 responses
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
24 Jan 2011 à 22:47
24 Jan 2011 à 22:47
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 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
25 Jan 2011 à 19:38
25 Jan 2011 à 19:38
In vlookup the first argument should be the FIRST column. in case this is not first columns dont use vlookup but use index-match.
