Dear Experts
I had to complete a huge excel cleaning job and was not provided time.
Please refer to the attached input and reference output tabs.
I have excel column, with names in various formats, I need to compare each cell of this with master database and if matching print the row number against column A.
What I tried:
I used Delimeter and split the dates, and combined based on date, segregated into another column, As my search column is less than the reference columns (not equal), I cannot use INDEX/MATCH, I believe, so I am using VLOOKUP but its tedious as I have to do several steps because given name and surname is different.
Thank you in advance.
Input file
| Target to search ( each of the cell below) - Date format DD/MM/YY | Search against this Column | Record 1D | Date |
| Tanya, Surname_25/12/18 | Names 1 | ||
| David Surname_11/12/12 | Names 2 Surname | ||
| Surname kenny_10/08/12 | Surname Name 3 | ||
| Surname, Thomas_12/06/19 | Name_Surname4 | ||
| Jean ven desol_12/04/18 | Tanya, Surname_25/12/18 | ||
| Craig McDermott | Peter Jones|_10/11/13 | Surname Name 4 | ||
| Vince_Surname_15/06/16 | Surname Name 5 | ||
| Eddy Murphy_23/10/18 | Surname Name 6 | ||
| Surname Name 6 | |||
| The objective is to search each cell of column A, which has the record with date after 5th March 12 and match with each of the cells of Column B and Print matched with row number of Column B. As shown in Tab Results Expected | David Surname_11/12/12 | ||
| Jean ven desol_12/04/18 | |||
| Surname kenny_10/08/12 | |||
| Eddy Murphy_23/10/18 | |||
| Jean ven desol_12/04/18 | |||
| Craig McDermott | Peter Jones|_10/11/13 | |||
| Vince_Surname | |||
Result
| Rearranged Search Names and Date | Date | Search against this Column | Record 1D | Date | Record Status (if matched, print the row) | ||
| Tanya Surname | 25/12/2018 | Names 1 | 6 | ||||
| David Surname | 11/12/2012 | Names 2 Surname | 11 | ||||
| Kenny Surname | 10/08/2012 | Surname Name 3 | Not available | ||||
| Thomas Surname | 12/06/2019 | Name_Surname4 | Not available | ||||
| Jean ven desol | 12/04/2018 | Tanya, Surname_25/12/18 | 12 | ||||
| Craig McDermott | 10/11/2013 | Surname Name 4 | 16 | ||||
| Vince Surname | 15/06/2016 | Surname Name 5 | 17 | ||||
| Eddy Murphy | 23/10/2018 | Surname Name 6 | |||||
| list continues | List continues | Surname Name 6 | |||||
| David Surname_11/12/12 | |||||||
| Jean ven desol_12/04/18 | |||||||
| Surname kenny_10/08/12 | |||||||
| Eddy Murphy_23/10/18 | |||||||
| Jean ven desol_12/04/18 | |||||||
| Craig McDermott | Peter Jones|_10/11/13 | |||||||
| Vince_Surname |
[Image Can Not Be Found][Image Can Not Be Found][Image Can Not Be Found]
Hi Anand,
Welcome to our forum!
Assuming your table starts in cell A1, you can use this formula:
=IFERROR(MATCH(A2,$B$1:$B$17,0),"Not available")
Mynda
Hi Mynda
Thank you, but Column A, the list to search and column C, has ( has diff name formats, multiple records of same name), so the match might fail unless I do some data cleaning, I believe.
Hello Anand,
It is always easier if we have a sample file showing the data plus the expected outcome. If the data is formatted differently, then it is advised to do a clean up. If you are familiar with Power Query then that might be the tool you need.
Hi Anand,
I took your 'Input' table and pasted it into cell A1 of an Excel worksheet. I then put the formula in cell E2. It works to return your 'result' column 'Record Status (if matched, print the row)'.
If your input is actually different, then please provide a sample file as Anders suggests.
Note: you can match multiple columns with an array formula e.g.
=IFERROR(MATCH(A2&B2,$C$1:$C$17,0),"Not available") enter with CTRL+SHIFT+ENTER. Mynda
