Index Match Formula

I am trying to use sheet cross-reference formulas to pull data from one sheet to another. I have a "Roster" Sheet that lists the names of new employees who have many different job codes. I want to only pull the Last Names, First names of employees that have specific job titles.

How can I use the index match formula to pull this data? The way I have it currently set up it is only pulling the specific name of the cell/row that I choose.

The columns I am trying to populate are Last Name and First Name and the job codes I want it to search for are alpha/numerical values such as: 400018, ct9400018

This is how I have the formula now.

=INDEX({Roster Range 1}, MATCH({Roster Range 3}, {Roster Range 2}, 0))



  • J Tech
    J Tech ✭✭✭✭✭

    Hi Andi,

    Please try =INDEX({Roster Range 1}, MATCH(TRUE, ISNUMBER(SEARCH({400018, ct9400018}, {Roster Range 2})), 0))


    If you want to use an OR statement instead of searching for multiple codes separately, you can modify the formula like this:

    =INDEX({Roster Range 1}, MATCH(TRUE, OR({Roster Range 2} = "400018", {Roster Range 2} = "ct9400018"), 0))

    This formula will check if the value in {Roster Range 2} equals either "400018" or "ct9400018". If either condition is true, it will return the corresponding value in {Roster Range 1


    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
  • I tried the second formula like this and it is coming back as invalid operation. I named my references to make it more clear which column I was pulling from

    =INDEX({GCO Roster Last Name}, MATCH(true, OR({GCO Roster Job Code} = "400018", {GCO Roster Job Code} = "ct9400018"), 0))

    When I do this one it comes back unparseable

    =INDEX({GCO Roster Last Name}, MATCH(true, ISNUMBER(SEARCH({400018, ct9400018}, {GCO Roster Job Code})), 0))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You will first need a text/number column in the source sheet to convert all job codes into text strings so that you have a consistent data type.

    =[Job Code]@row + ""

    Then to search for multiple job codes, you will need an INDEX/COLLECT.

    =INDEX(COLLECT({GCO Roster Last Name}, {GCO Roster Job Code Helper}, OR(@cell = "ct9400018", @cell = "400018")), 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!