Using Index and Match to Retrieve Specific Data from Dupicate Rows

[dfmurphy8]

Hi,

In the attached table I am trying to use the INDEX and MATCH functions to determine specific data from rows with duplicate search rows.

I have managed to return the correct information for each statement separately ie "Effective To" column is blank or is not blank (refer Cell F4 and G4) but when I try and combine these statements within an IF statement only the first statement is addressed. On row 86 #N/A values are returned instead of the calculation from the second INDEX/MATCH statement.

Any assistance would be greatly appreciated.

Thank you

Duncan

[AliGW]

For the formula in G3, there is no match for this section of the formula:

($I$3:$I$68=B2)*($N$3:$N$68=0)

[dfmurphy8]

Thank you Ali for the reply. Yes I will use an IFERROR to make that 0.

I am trying to understand why cell C86 does not return 24. I am not sure whether the FALSE part is the IF statement is returning a #N/A for this cell instead of 24.

Maybe I cannot use IF and INDEX and MATCH in this way?

Duncan

[AliGW]

It's the opening statement in the formula that is returning the error: click on the yellow warning diamond that pops up next to the cell to see it being evaluated.