Need Help Writing a Logic Formula Comparing Cells Values

[BlakeT]

Hey everyone,

Thank you for any time and assistance. I believe this should be pretty simple but with my limited experience with nesting logic functions, I can't figure out the right choices to implement what I need.

I need a formula to return True or False comparing combined cells H2/I2, combined cells K2/L2, and cell AA2 (where if they all match, True, if they don't, False). However, if cells K2/L2 are blank, the function does not include those cells in the comparison.

Columns H and I are first and last name, K and L are first and last names from a previous submission, and Column AA is the first and last name from a reference sheet that displays the name altogether. The goal is to make sure all iterations match up, but if there is no previous submission (columns K and L), the blank cells do not count against the comparison and columns H and I can simply be compared directly against column AA.

Thank you again for taking a look if at all.

 

[KRice]

Here is a start. How are the names connected in AA? I've assumed a single space, but spaces and commas, for example, may lead to mismatches (as in the last row where two spaces are between first and last name in AA7). Some trimming may be needed to fine tune this.

MrExcel_20220419.xlsx
	H	I	J	K	L	M														AA	AB	AC
1	Fname2	Lname2		Fname1	Lname1															FandLnameRef		Result
2	Frank	Jones		Frank	Jones															Frank Jones		TRUE
3	Frank			Frank	Jones															Frank Jones		FALSE
4	Frank	Jones			Jones															Frank Jones		FALSE
5	Frank	Jones		Frank																Frank Jones		FALSE
6	Frank	Jones																		Frank Jones		TRUE
7	Frank	Jones		Frank	Jones															Frank Jones		FALSE
Sheet4

Cell Formulas
Range	Formula
AC2:AC7	AC2	=AND(TEXTJOIN(" ",,H2,I2)=AA2,OR(AND(K2="",L2=""),TEXTJOIN(" ",,K2,L2)=AA2))

 

[BlakeT]

Kirk, I can't thank you enough for how quickly this fixed everything! I'm going to really sit with the formula and unpack it so I can use this going forward in other ways but this was a massive help and I really appreciate it. Thank you.

 

[KRice]

You're welcome. I'm happy to help!