Formula : =IFERROR(INDEX(D:D,MATCH(1,(B:B="Flit")*(C:C="John Carl"),0)),"") Result : ID1004 Problem: In situation 2, if it only looks for cells with "Carl" or "*Carl*" it doesn't work. Any suggestion how will I use the situation 2 having an index match with multiple criteria but can still look for cells that contains such specific string. Join 400,000+ professionals in our courses: learn how to lookup one value in Excel and return multiple matches. VLOOK Generic formula syntax to lookup values with INDEX and MATCH with multiple criteria is: =INDEX (range1, MATCH (1, (criteria1=range2)* (criteria2=range3)* (criteria3=range4), 0)) Where, Range1 is the range of cells to lookup for values that meet multiple criteria. Criteria1,2,3 are cell references to test multiple criteria. Introduction. For decades, INDEX and MATCH have been the go-to solution for handling complex lookup problems. Unlike VLOOKUP, INDEX and MATCH are based on numeric positions: the MATCH function locates the position of a value, and the INDEX function retrieves a value at that position. This approach makes INDEX and MATCH highly versatile, at the cost of more configuration. The most popular way to do a two-way lookup in Excel is by using INDEX MATCH MATCH. This is a variation of the classic INDEX MATCH formula to which you add one more MATCH function in order to get both the row and column numbers: INDEX ( data_array, MATCH ( vlookup_value, lookup_column_range, 0), MATCH ( hlookup value, lookup_row_range, 0)) How to Use INDEX + MATCH Formula to Match Multiple Criteria? We explain using the INDEX+MATCH formula to match multiple criteria for rows and columns with examples. You can download this Index Match Multiple Criteria Excel Template here - Index Match Multiple Criteria Excel Template. Example #1 - INDEX + MATCH Formula. ePRmj.

formula index match multiple criteria