Wrapping an if function around an Index/Match function - need help ;)

[tonyridino]

Hi guys, this is my first post on the forums! I have a complicated problem that I need help with. I developed a tool for my work to automate the process of hiring employees.

I currently have this working forumla in cell G7:

INDEX(R9:R6404,MATCH(1,(W9:W6404=G4)*(Q9:Q6404=D6),0))

This index function looks through a massive list of data to return a single value (job code) if it meets 2 criteria: If G4 - Job title and D6 - city both match the values that are already in cells G4 and D6, it returns the corresponding value of job code (a value listed in the same row of the database, in a different column). For instance, an employee in San Diego, CA with the job title of Electrician 1 will have a specific job code that is different than if he was an Electrician 1 in a different city, such as New York.

Make sense so far?

The problem is that if there are no employees with that particular job title and location, the index/match function will return nothing (a blank cell).
I need help with this part:
If the function returns nothing, I need to imbed another function to be run after the index/match function returns a blank that re-runs the process, but instead of looking at cells G4 (job title) and D6 (city), I need the forumla to look at G4 (Job title) and E6 (State).

Is it possible to have this happen? I'm really bad with embedding!
Thanks in advance.

[Tony Valko]

The problem is that if there are no employees with that particular job title and location, the index/match function will return nothing (a blank cell).

If the criteria are not met the formula should return a #N/A error.

You could try something like this...

=IF(INDEX(R9:R6404,MATCH(1,(W9:W6404=G4)*(Q9:Q6404=D6),0))="",INDEX(R9:R6404,MATCH(1,(W9:W6404=G4)*(Q9:Q6404=E6),0)),INDEX(R9:R6404,MATCH(1,(W9:W6404=G4)*(Q9:Q6404=D6),0)))

Still array entered.

[tonyridino]

Hi Bill,
Thanks for that!

[Tony Valko]

You're welcome!