Aligning 1st and Last name column in Excel with similar .jpg file name in separate column for SQL

[aknadler]

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.

[#2]

so maybe if you provided a sample file we might understand better but if all you need is to have a 3rd column that says what the jpg filename is and that filename is Lastname_Firstname.jpg then couldn't you just use (assuming firstname in col A and lastname in col B)
=B1&"_"&A1&".jpg"
and fill down or in excel 365 assuming rows 1:1000
=B1:B1000&"_"&A1:A1000&".jpg"

[#3]

@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

[#4]

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.

[#5]

@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!

[#6]

ah wait I notice 2 things.
a) the names don't always match. In your example above you have:
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
and so you have a "2" after Erika and Brandon and you also have Debi instead of Deborah and Josh instead of Joshua
b) it has JPG instead of jpg
you can try the following version that takes last name and first 2 characters of first name instead but know a) it can pull the wrong result and b) it won't find nicknames that start different like "Belle" instead of "Isabelle"
=LET(n,B1:B1000&"_"&LEFT(A1:A1000,2)&"*", IFERROR(INDEX(D1:D1000, XMATCH(n, D1:D1000,2)), ""))

[#7]

https://answers.microsoft.com/en-us/msoffice/forum/all/populate-cell-based-on-similar-text/82bde8c1-...


fuzzy match
find minium edit distance?

[#8]

You can use the **CONCATENATE** function to line up and match the human name to the .jpg file name horizontally on a different column. The **CONCATENATE** function allows you to combine text and values into a single string.

In your case, you would use the **CONCATENATE** function to combine the last name and first name from the first two columns, and then append the .jpg extension. For example, if the last name is "Doe" and the first name is "John", you would use the following formula:

```
=CONCATENATE(LastName, "_", FirstName, ".jpg")
```

This would create the string "Doe_John.jpg". You could then copy and paste this formula into the third column, and it would automatically match the human name to the .jpg file name.

Here is an example of how you would use the **CONCATENATE** function to match the human name to the .jpg file name in Excel:

```
=CONCATENATE(A2, "_", B2, ".jpg")
```

In this example, the first two arguments of the **CONCATENATE** function are the cells that contain the last name and first name. The third argument is the .jpg extension. The result of the formula would be the .jpg file name, which would be placed in the cell that contains the formula.