Return index of cell that contains match on two strings

Question

I'm trying to find the index for a cell that contains two string matches. For example, I have a first name and last name. I need to find a cell that contains both of these names in it.

Possible combinations of names could be the following Jobs, Steve or Jobs II, Steve or Jobs, Steve Apple or Jobs Jr, Steve Apple. You get the drift.

I need to return the index of the first matching cell that contains both the first and last name.

Here is my attempt. My thought was to try and find a match with the last name first and then see if that match contains the first name also:

var firstName = 'Steve';
var lastName = 'Jobs';
var columnOfNamesArr = ['Participants','Wozniak, Steve','Gates, Bill','Cook, Tim','Jobs, Mark','Jobs, Steve Apple','Musk, Elon'];

var lastNameIndex = columnOfNamesArr.findIndex(s => s.indexOf(lastName) >= 0);
var nameIndex = columnOfNamesArr[lastNameIndex].findIndex(s => s.indexOf(firstName) >= 0);

I get the following error though on the line that starts var firstNameIndex... TypeError: Cannot read properties of undefined (reading 'indexOf')

What am I doing wrong and is there a way to do this all in one search instead of two?

Edit: Adding more details around how columnOfNamesArr is created.

I pull in a column like this:

Participants
Wozniak, Steve
Gates, Bill
Cook, Tim
Jobs, Mark
Jobs, Steve Apple
Musk, Elon

and I put it into a variable like this:

columnArr = namesSht.getRange('A1:A'+ LastRow).getValues();

and then I flatten like this:

var columnOfNamesArr = columnArr.map(r => r[0]);

That's when I try to run the findIndex and includes

var nameIndex = columnOfNamesArr.findIndex(s => s.includes(lastName) && s.includes(firstName));

but I get the error code I mentioned above.

Answer 1

In your showing script, columnOfNamesArr[lastNameIndex] is a string value. When you want to use findIndex, it is required to use an array. I think that this is the reason for your current issue. If you want to retrieve the first index of the array columnOfNamesArr when both firstName and lastName are included, how about the following modification?

Modified script:

var firstName = 'Steve';
var lastName = 'Jobs';
var columnOfNamesArr = ['Participants', 'Wozniak, Steve', 'Gates, Bill', 'Cook, Tim', 'Jobs, Mark', 'Jobs, Steve Apple', 'Musk, Elon'];

// var index = columnOfNamesArr.findIndex(s => s.indexOf(lastName) >= 0 && s.indexOf(firstName) >= 0);
var index = columnOfNamesArr.findIndex(s => s.includes(lastName) && s.includes(firstName));

console.log(index);
console.log({ value: columnOfNamesArr[index] });

When this script is run, 5 is shown in the log.

Note:

• If both firstName and lastName are included in the inputed array and you want to retrieve the indexes as an array, how about the following modification?

  var firstName = 'Steve';
  var lastName = 'Jobs';
  var columnOfNamesArr = ['Participants', 'Wozniak, Steve', 'Gates, Bill', 'Cook, Tim', 'Jobs, Mark', 'Jobs, Steve Apple', 'Musk, Elon'];
  
  var res = columnOfNamesArr.reduce((ar, s, i) => {
    if (s.includes(lastName) && s.includes(firstName)) {
      ar.push(i);
    }
    return ar;
  }, []);
  console.log(res);
  

Reference:

• findIndex()