Aug 02 2023 08:56 AM - edited Aug 02 2023 09:34 AM
I have an excel spreadsheet. I have a column listed for first name and another column for last name. I have another column for JPEG images. The file name is similar to the name which is Lastname_Firstname.jpg. I need to line up and match the human name to the .jpg file name horizontally on a different column. Can this be done using a function or formula? I am initially importing the file into SQL. I understand the Vlookup and Xlookup functions but is there a direct function or formula that I can use that will automatically pull the .jpg filenames from a separate column and align them next to the column of names. Assuming I would use the Last name for the similar data point as it would be shared between the human name and .jpg name.
Aug 02 2023 11:30 AM
Aug 02 2023 06:21 PM
@mtarler Thank you for your response. See sample file. Names have been changed, but basically I have copied the file name from the folder and pasted into a column on the document. There are 684 names and over 1000 .jpg file names, so they do not line up if I try to ascend and alphabetically. I am trying to get the Lastname_Firstname.JPG file to line up as seen in rows 10-13, I inputted these in manually.
I appreciate your assistance with this.
Thank you!
LastName | FirstName | ImageData | JPEG |
abbot | Paul | Ackerman_Erin.jpg | |
Abbott | Adam | Acosta_Alexandria.JPG | |
Abbott | Jeff P. | Acosta_Angie.jpg | |
Adye | Austin | Acosta_Olga.JPG | |
Allen | jill | Adams_Mikey.JPG | |
Allen | Chandra | Adams_Sam.JPG | |
Almanza Flores | Angel D. | Adams_Thomas.JPG | |
Alvarez | Veronica | Adriana.JPG | |
Amaya | Erika | Amaya_Erika2.JPG | Adye_Austin.JPG |
Anderson | Logan | Anderson_Logan.JPG | Aerni_Shannon.jpg |
Archer | Deborah | Archer_Debi.JPG | Affolter_Brandon.jpg |
Archibald | Joshua | Archibald_Josh.JPG | Affolter_Brandon2.JPG |
Arneson | Bryan | Abbot_Paul.JPG | |
Aufdermauer | Faith N. | Abbot_Adam.JPG | |
Ayala Sanchez | Ana G. | Abbot_Jeff | |
Ayers | Jon | Adye_Austin.JPG | |
Baladez | Mabelyn | Allen_Jill.jpg | |
Bales | Justin | Allen_Chandra.jpg | |
Barber | Richard | Arneson_Bryan | |
Barbour | Maximilian L. | Aufdermaur_Faith.jpg |
Aug 02 2023 07:04 PM - edited Aug 02 2023 07:09 PM
so basically you only want the file name listed if it exists in column D? try this:
=LET(n,B1:B1000&"_"&A1:A1000&".jpg", IF(ISNUMBER(XMATCH(n, D1:D1000)), n, ""))
If you have an older excel you can use:
=IFERROR(VLOOKUP(B2&"_"&A2&".jpg", $D$1:$D$1000, 1, 0),"")
and then fill down
so in answer to your question, I guess not, this is as direct as I can think.
Aug 03 2023 09:33 AM
@mtarler Thank you for the information. I appreciate your discerning and detailed assistance with this.
Yes, that is correct I would only want the file name listed if it exists in Column D. I am currently using the MS Excel latest Rev.
If I plug in the function =LET(n,B1:B1000&"_"&A1:A1000&".jpg", IF(ISNUMBER(XMATCH(n, D1:D1000)), n, "")) it highlights rows A B and D in Red, Blue and Purple and creates a spill column but I do not see any data in the spill column other than a few 0's. Not being an Excel guru, I would think the result should show the matched names in the spill column?
Thanks!
Aug 03 2023 10:44 AM
Aug 03 2023 04:58 PM
Aug 03 2023 05:09 PM