Forum Discussion
INDEX/MATCH with multiple criteria -- if value isn't found, ignore it, and search for something else
I *think* this is what you're looking for:
=INDEX(Price, MATCH( IF( ISERROR( FIND("/",Division) ), Division, TRIM( LEFT( L11, FIND( "/", Division)-1 ) ) ), Size, 0) )
For INDEX-MATCH, the logic is: Index on the price by matching a calculated value in the Size column, using a match-type of zero.
For the calculated value, the logic is: If "/" does NOT exist in the Division text, return the Division text. Otherwise, return the TRIMmed text before the "/" in the Division cell.
=INDEX( Price,
MATCH(
IF(
ISERROR( FIND("/", Division) ),
Division,
TRIM(
LEFT(
L11,
FIND( "/", Division)-1 ) ) ),
Size,
0) )