Index Match Formula

Andi Heggem

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

Hi Andi,

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

or

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

Andi Heggem

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

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)