I've got these two tables on two different worksheets.
A1: ID
B1: Name
C1: Locality
D1: ID_2
E1: Status
A1: ID
B1: Name
C1: Locality
D1: Status
Basically I need to match the two tables via "Name". If the names are similar I want to fill in "ID_2" with "ID" in WS2. I also want to display "Y" in status if there's a match.
Is there a formula that does this? Or do I have to use VBA.
Office Version: 2003
Hi,
Have you tried using advanced filter or conditional formatting ?
V
Hello,
You could use either vlookup or hlookup to fill the value in from the other table. Based on the information you provided you would need to use vlookup. VLOOKUP Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.
You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of employees contained in the range A2:C10. The employees' ID numbers are stored in the first column of the range or Column A and start on Row 2 (Normally Row 1 contains the headers like ID, Name , Locallity, Status). If you know the ID number, you can use the VLOOKUP function to return either the department or the name of that employee.
Your best bet is to check the help section of the version of excel you are running for specific information. The command syntax can vary a little between versions.
Hi thanks for your replies. Wouldn't the VLOOKUP require exact data. Say the Name in one table is Jean Paul Brown but in the other table it is Jean Brown, would the VLOOKUP be able to match them? The two IDs are different that's why I'm using names.
Hello,
Yes it would and they would not match if the names are like the example you provided. But technically Jean Brown is not a match for Jean Paul Brown and it should not be. If it is a small data base then you could try splitting the names apart and then matching off of first and last name. There is an option in VLOOKUP to go to the next higher match. That would work if the names were like:
Brown, Jean Paul
Brown, Jean
And if you were always looking using the smaller name toward a sheet with the longer name. You would probably get more matches than you have initally. What you are trying to do is reeally better suited for being completed with a programming language like Perl or Python. The allow you to split strings into individual words and compare the different parts. Excel is a spreadsheet with some database qualities and you could use it to split the names apart and compare them but what you really need to do is fix the input into the two files so that they use the same data formats or have a common id between them.
The other thing you are going to have to deal with is should Jon Smith match Jonathan Smith and things like that are going to take either a human to evaluate all of the parameters and decide, or be coded into a fairly complex program that takes into account all possibilities.
Hope this helps.
Thanks, the thing is that there are over 4000 records on each worksheet. I'll use VLOOKUP to sort some of them and I'll do the rest manually.
And how can I do this? I've searched for this feature in VLOOKUP but only found "the next highest value".
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.