Index Match Formula with Multiple Criteria?

Hello,

I'm trying to create an index that has multiple criteria. I'm not sure if I should do this using an "IF" formula, or if I need to incorporate the "AND" function into the formula. Or if I need to go in another direction to accomplish this.

What I need to do is return the "Rate" based on the "Crew Type", and the "Number of People on the Crew". For example, a "Trim Standard Lift" Crew with 2 people has a different rate than a "Tree Trimming" Crew with 3 people. There are 8 different "Crew Types", and crews range 2-6 people.

This is an example of the data from each row. This sheet is where I'll be be adding the row to each formula.

This is an example of the sheet I am indexing that contains the rates.

Is anyone able to point me in the right direction of how to build this formula?

Thanks in advance!

Find more posts tagged with

Formulas

Project Management

Accepted answers

MCorbin

I usually create a formula column that joins the 2 columns I'm looking for in both sheets:

"Lookup"

=join([Crew Type]@row:[# of People on Crew]@row)

Then use your Lookup Column in the Index/Match formula as your lookup value

=Index({Rate},Match(Lookup@row,{Lookup},0)

(If other people access the sheet, I lock the Lookup Column and either hide it or move it to the far right....)

All comments

MCorbin

I usually create a formula column that joins the 2 columns I'm looking for in both sheets:

"Lookup"

=join([Crew Type]@row:[# of People on Crew]@row)

Then use your Lookup Column in the Index/Match formula as your lookup value

=Index({Rate},Match(Lookup@row,{Lookup},0)

(If other people access the sheet, I lock the Lookup Column and either hide it or move it to the far right....)

Shelby

@Amie LaCharite

Have you found a solution for the index match for multiple criteria? I desperately need this as well.

Amie LaCharite

@Shelby

No, unfortunately we did not. We used the 'Join' function as suggested, then decided to go in a different direction all together.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.