Index/Match Criteria

[jharvey87]

Hi everyone,

Is it possible to do an index/match function with 3 criteria? This is my current formula.


=IFERROR(INDEX(Master!$G$4:$G$6770,MATCH($B7&$J$5,Master!$B$4:$B$6770&Master!$H$4:$H$6770,0)),"")

B7 = Contract Number
J5 = Bonding Type

I'm wanting to add a 3rd criteria for project name. So for example, could it also look on the Master tab, column "AL" for when the cell value is equal to APWP? So it would match Contract #, Bonding Type, and Project Name.

Thanks for the help.

[PCI]

Try ... NOT TESTED

MATCH($B7&$J$5&"APWP",Master!$B$4:$B$6770&Master!$H$4:$H$6770&Master!$AL$4:$AL$6770,0))

Else send a short Excel sample

[dflak]

When I am trying to select something with more than one criteria I often use a helper column with what I call a "composite index."

So if you are looking for a record whose uniqueness depends on a combination of contract number, bonding type and project name, then I make a helper column with the formula [contract number] & ":" & [bonding type] & ":" & [project name]. The delimiters aren't really necessary, but they make the composite index easier to read.