Hi,
The below formula currently fetches the 2nd value, but it is actually the first corresponding in a cell where there is actually a value, since the first corresponding match is a blank cell. Could we revise the formula to fetch the second real value (text) ignoring all the corresponding blank cells?
The below formula currently fetches the 2nd value, but it is actually the first corresponding in a cell where there is actually a value, since the first corresponding match is a blank cell. Could we revise the formula to fetch the second real value (text) ignoring all the corresponding blank cells?
Excel Formula:
=IF(SUMPRODUCT(--(INDEX($A$2:$AU$500,,MATCH("Nr",$A$1:$AU$1,0))=RIGHT(INDEX($A$1:$CU$500,ROW($A2:B2),MATCH("UMR",$A$1:$CU$1,0)),9))))>=1,IFERROR(INDEX($AM$1:$AM$500,SMALL(IF($J$1:$J$500=RIGHT(INDEX($A$1:$CU$500,ROW($A2:B2),MATCH("UMR",$A$1:$CU$1,0)),9),ROW($AM$1:$AM$500),1000000),2)),""))
Last edited by a moderator: