I have a database with different names for the same phone number. But some of the mobile numbers contains same names as listed below. So I have to Match mobile numbers having same names as shown in the below pic. Ignore blank cells. Its a large database, so number of columns and rows may vary. Please find a solution for this issue. In the below sheet B2 and C2 are different, so F2 should be 'MISMATCH'. B3,C3 and D3 are equal then f3 should be 'MATCH' and so on. PFAenter image description here

1 Answer 1


Use map() and reduce(), like this:

  mob, A2:A, 
  name1, B2:B, 
  rest_, lambda(c, offset(c, 0, 1, 1, columns(C2:E))), 
  map(mob, name1, lambda(m, n, 
      m = "", 
      reduce(true, rest_(n), lambda(b, r, and(b, or(r = "", r = n)))), 

See map() and reduce().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.