LinkBack URL
About LinkBacks
Morning,
How can i use a vlookup to check 2 sheets of data to make sure a reference exists in both lists, and bring back that reference into a template?
Morning,
How can i use a vlookup to check 2 sheets of data to make sure a reference exists in both lists, and bring back that reference into a template?
Hi
Your question is rather vague - check out the rules.
This may help.
http://office.microsoft.com/en-us/ex...005209335.aspx
If I helped, please don't forget to add to my reputation. (click on the star below the post)
If the problem is solved, please: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.
Failure is not falling down but refusing to get up.
Hi,
My apologies.
I have 2 sources of data, i need to check both columns of data to make sure a reference number is aparant in both lists. (like checking one list against the other)
Not sure if it was something like =VLOOKUP(list1A1:A300,List2A1:300,1,1,0)
Hi - it's really going to be a help if you could upload a sample of your worksheet
A non VBA way of doing this would be to use the ISNA and and function
VBA would require a user-defined function which I'm not particularly great with but the above should give you what you're after.
Please Login or Register to view this content.
Maudise
Attached as requested,
If the value in sheet 2 is in sheet 1 then that value need to be shown.
Has this helped?
Hi
Sorry, I thought you'd got a solution.
Yes, in Sheet1 in B2 put
Copy downPlease Login or Register to view this content.
This will identify those matched.
Last edited by Russell Dawson; 05-17-2012 at 10:20 AM.
Brilliant, your a star!
My pleasure. Don't forget to mark as solved and a star would be nice
Is there a way of pulling through a whole row of data once the reference has been checked rather than just that one cell!?
If you drag the formula down from B2 and that then checks each entry in Sheet1 against all entries in Sheet2 and give a result of all matches in column B. By row I assume you mean column.
Sorry i should have explained my query a bit more, apologies, using this same type of formula, can it be used in a similar way but bring through a row of data, for example if there was data in A1, B1, C1, D1.
Can all this data be shown if the reference is on both sheets?
You can't beat reality. Please try to show the real worksheet or at least a representative sample of what you have and what you expect.
As requested.
In the sheet attached, on sheet 1 i have the full data and on sheet 2 i have the references that i need to compare to sheet 1. The formula used matches the references then pulls the matched reference as a result.
My question is instead of just the reference been shown, can the whole line of data be shown instead, eg cells A9,B9,C9,D9,E9
Attachment as requested, not sure if it attached above.
Can anyone help on this at all?
Hi Ken,
Sorry, I missed your post - delayed email?
You could have a simple entry in the columns where you expect the data from the other four columns.
In H1 put
In I1 putPlease Login or Register to view this content.
and so on. Then copy down.Please Login or Register to view this content.
I do hope I understood you correctly.
Regards
Russell
Hi thanks for coming back,
It seems to return all information for all rows, i only wanted the info for the cases that were matched on both sheets suing the VLOOKUP formula.
Sorry - didn't test it beyond filled cell. Duh!
Use this in G1 and across and down.
I've set it so that when you copy across it will pick up relevant data. Also set absolute values to match critical columns.Please Login or Register to view this content.
Lovely, works a treat, thank you!
Following on from this, is there a macro i can use to delete all the lines that don't have the required information once the vlookup has been done?!
You could sort column G (Z-A) and then delete all below as they are clearly not matched
Lovely,
I've now put this into production and wondered what would change if the data was all in once sheet, the formula is in column J, i can't see to adjust it to bring back the whole data rather than just the reference.
The reference to column H in the formula needs to be absolute for H so in J2
=IF(ISERROR(MATCH($H2,$A:$A,0)),"",A2)
Then drag across and down.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks