finding matches from separate lists, and then concatenating

[ibos]

I have have thousands of photos I rename every couple months or so, and use excel to do it. The problem is that I have two lists. One is the actual photos, and I paste the file names into one column. The other list is a database which contains several columns of data which are used in the file names. What I've done for a while is concatenate this data into a new column, and use conditional formatting to highlight which photos were not in my database, and entries in the concatenated column that we have data for, but don't have photos.

What I'd like to do is search cells in my files column (F) and see if there is a match in my concatenated data column (E). If a cell in that column (E) matches, I want to take the ID number from it's row (A) and add it to the end of the filename.
IE - for my data, Joe Brown doesnt have a photo, and Dave Baker wasn't in my database, so they'd get left out. John Doe, Jane Doe, Dan Smith, and Julie Walters all have data and photos, so I'd want a new column that ultimately gives me results that show "zzz_doe_john101" "xxx_doe_jane102" "zzz_Smith_Dan104" and "xxx_Walters_Julie105" as seen below, and omitting spaces or entries for photos that had no data or data that had no photos.
I think I know what I need (IF, MATCH, INDEX, and CONCATENATE), but I just don't know how to put it all together.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ibos]

I'm on excel for mac, version 16.63.1

 

[Peter_SSs]

Thanks for updating your platform, but the version information you mention is not what we are after, It would be a choice of 1 or more of the options below as shown when you go to your account details. It is this information that tells us what functions and resources you have in your Excel.

 

[ibos]

I'd gladly tell you, but have no idea where that information can be found. How can I access it?

 

[Peter_SSs]

I'd gladly tell you, but have no idea where that information can be found. How can I access it?

Read post #2 above carefully - it tells you.