Find matching text and matching unique ID

Hi guys,

I've been looking for a solution to a manual task I have to carry out with no avail - I'm not sure whether it can be completed using excel tools or if it will need some VBA code so I thought I would reach out to some experts...

Basically I have 2 separate lists that should have a few matching pieces of text, both of which have a unique ID to their respective list. I'm trying to match the text so that I can easily find the two unique numbers, how would I do this? I'll put together a short example below...

ID1 Text1 Matching ID2 ID2 Text2
1 Aberdeen 3 1 Liverpool
2 Glasgow 2 Dundee
3 Manchester 3 Aberdeen
4 London 4 Coventry

What I would like to do is look for each entry under Text1 in the Text 2 column and, if they match, copy the ID2 into the Matching ID2 column. So in the example above I would look for "Aberdeen" in Text2 and see it matches under ID2, so I would populate Matching ID2 with a "3". I would then look for "Glasgow" in Text2, then "Manchester" and so on.

This is a simplified version of the data involved, so I'd imagine the solution will look for text within a cell references as opposed to looking for specific text. I had a look into VLOOKUP and INDEX functions but I couldn't figure out if they would work in this situation.

Any help would be appreciated, thanks.

EDIT: I've noticed the formatting changes once I submit the thread, I'll attach a screen grab of the workbook.Capture.JPG

Originally Posted by NickyC

Hi
assuming your headings are in cells A1:E1 and your data in cells A2:E5 try this formula

Please Login or Register  to view this content.

Hi Nicky,

Many thanks for the quick reply. The code appears to work in my simplified workbook but in the actual workbook I only get "#N/A" and a few "#VALUE!" results, unfortunately no ID's are copied across. I modified the code slightly to the following:

Please Login or Register  to view this content.

The reasons being: Cell E2 is ID2, C2 is Text1 and the range is fairly substantial. I'm assuming my error is due to the complexity of the text under Text1/Text2, maybe there are minor differences...I shall investigate further. Is there perhaps a way to use approximate matches such as in the VLOOKUP function?

EDIT: I've noticed that in Text2 the text is introduced by a number, so going back to the example it would read "1.Aberdeen" (no spaces between . and A), is there a way around this?

Thanks again for your help.