INDEX MATCH W/ 2 criteria and partial match

Kelly W

If I enter "HII-1607302" on a separate sheet I want it to search the Project Number for that partial match and return the value in the Budgeted Multiplier Column into a cell. Two issues: The Project # (HII-1607302 for this example) is only a portion of the text in the Project column, so it needs to be a partial match not an exact match and 2nd issue - multiple rows will meet this first criteria - so I need it to only return the budgeted multiplier value that is greater than 0.

Thank you!!!

KPH

Hi @Kelly W

I would switch INDEX MATCH to INDEX COLLECT CONTAINS.

COLLECT allows for multiple criteria and CONTAINS allows for partial matches.

The formula looks like this:

=INDEX(COLLECT([Budgeted Multiplier]:[Budgeted Multiplier], Project:Project, CONTAINS([Project ID]@row, @cell), [Budgeted Multiplier]:[Budgeted Multiplier], >0), 1)

You need two columns for the results sheet (shown in pink below). One with the Project ID in (the bit you want to look for in the project string) and one with the formula in.

The formula will collect the values in the Budgeted Multiplier column where the Project column contains the data in your Project ID cell and where the value in the Budgeted Multiplier column is greater than 0.

I would wrap the whole thing in an IFERROR to return blank if there is no match (which is more visually attractive than an error message).

=IFERROR(INDEX(COLLECT([Budgeted Multiplier]:[Budgeted Multiplier], Project:Project, CONTAINS([Project ID]@row, @cell), [Budgeted Multiplier]:[Budgeted Multiplier], >0), 1), "")

Note - if you have two rows with the same Project ID in the Project string that both have Budgeted Multiplier greater than 0, the first will be returned.

Here is an illustration:

You will probably want to put the pink part in a different sheet and use cross sheet references, but it is easier to explain without these. Let me know if you need anything else.