Need Help Writing a Logic Formula Comparing Cells Values

BlakeT

New Member
Joined
Apr 23, 2020
Messages
9
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
2,232
Office Version
  1. 365
Platform
  1. Windows
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
HIJKLMAAABAC
1Fname2Lname2Fname1Lname1FandLnameRefResult
2FrankJonesFrankJonesFrank JonesTRUE
3FrankFrankJonesFrank JonesFALSE
4FrankJonesJonesFrank JonesFALSE
5FrankJonesFrankFrank JonesFALSE
6FrankJonesFrank JonesTRUE
7FrankJonesFrankJonesFrank JonesFALSE
Sheet4
Cell Formulas
RangeFormula
AC2:AC7AC2=AND(TEXTJOIN(" ",,H2,I2)=AA2,OR(AND(K2="",L2=""),TEXTJOIN(" ",,K2,L2)=AA2))
 
Solution

BlakeT

New Member
Joined
Apr 23, 2020
Messages
9
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Dec 9, 2003
Messages
2,232
Office Version
  1. 365
Platform
  1. Windows
You're welcome. I'm happy to help!
 

Forum statistics

Threads
1,176,238
Messages
5,902,080
Members
434,938
Latest member
distalnyovac07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top