Need a vlookup formula to compare 2 different columns from 2 different work sheets in the same .xlsx. Where field values match copy a value from one sheet to the other.
Looking to use VLOOKUP to compare two different columns from two different sheets for matching value, and where value matches in Sheet A Column A and Sheet B Column A, copy values from Sheet B Column B to Sheet A Column B.
So.
Example.xlsx
Where value in column A matches B.
SheetA SheetB SheetA becomes
Column A Column A Column B Column A Column B
1 2 Yes 1
2 3 No 2 Yes
3 5 No 3 No
4 0 Maybe 4
Formula I tried.
=VLOOKUP($A$2,SheetB!A2:A4
No matter what i try i cannot get it to reference a value correctly in SheetB, even though I know there is one that matches $A$2 from SheetA.
So.
Example.xlsx
Where value in column A matches B.
SheetA SheetB SheetA becomes
Column A Column A Column B Column A Column B
1 2 Yes 1
2 3 No 2 Yes
3 5 No 3 No
4 0 Maybe 4
Formula I tried.
=VLOOKUP($A$2,SheetB!A2:A4
No matter what i try i cannot get it to reference a value correctly in SheetB, even though I know there is one that matches $A$2 from SheetA.
ASKER
Hi, Thanks for the response!
The way I understood it was the "2" value was the column data I was looking to copy over from column 2 table B to table A column 2 if there was a match for A2 somewhere on SheetB.
When I run the formula through error checker in Excel I get the correct value for A2, and it picks up SheetB and its values, but still get a ref error when running the final step.
I tried your formula but am not getting any data back.
The way I understood it was the "2" value was the column data I was looking to copy over from column 2 table B to table A column 2 if there was a match for A2 somewhere on SheetB.
When I run the formula through error checker in Excel I get the correct value for A2, and it picks up SheetB and its values, but still get a ref error when running the final step.
I tried your formula but am not getting any data back.
Hi,
Check the attached file and if it's not enough to solve the issue, please share workbook with dummy data that contains the error you got so we can clear it out.
VLookupEE.xlsx
Check the attached file and if it's not enough to solve the issue, please share workbook with dummy data that contains the error you got so we can clear it out.
VLookupEE.xlsx
Sometimes the 'numbers' are entered as text values and the lookup does not seem to work. This can be caught with an extra conversion step:
The first lookup tries number-to-number and the second forces the number to a text for text-to-text lookup.
(Hope our belated answer was useful)
=IFERROR(VLOOKUP(A2,SheetB!A:B,2,0),IFERROR(VLOOKUP(A2&"",SheetB!A:B,2,0),""))The first lookup tries number-to-number and the second forces the number to a text for text-to-text lookup.
(Hope our belated answer was useful)
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Try this function =IFERROR(VLOOKUP(A2,SheetB
In your function you set the value of column Index as 2 while you have only 1 column in Lookup table which is A, so your function won't return result you can change your function to be :
=VLOOKUP(A2,SheetB!$A$2:$B