I am using index match as a replacement to vlookup formulae and i want it set up so that if the result is N/A OR 0 then the cell is left blank
i have two working formulae, one that can return blank if the cell is N/A, one that returns blank if result is 0, but i cannot seem to figure out how to nest them into one formula?
This one returns blank if cell is NA
IF(ISNA(MATCH($B5,'[20120702 Overview Health and Social Care Market.xls]Current Compliance'!$A$13:$A$17739,0)),"",INDEX('[20120702 Overview Health and Social Care Market.xls]Current Compliance'!$N$13:$N$45000,MATCH($B5,'[20120702 Overview Health and Social Care Market.xls]Current Compliance'!$A$13:$A$17739,0)))
this one returns blank if cell is 0
=IF(INDEX('[20120702 Overview Health and Social Care Market.xls]Current Compliance'!$N$13:$N$45000,MATCH($B6,'[20120702 Overview Health and Social Care Market.xls]Current Compliance'!$A$13:$A$17739,0))=0,"",INDEX('[20120702 Overview Health and Social Care Market.xls]Current Compliance'!$N$13:$N$45000,MATCH($B6,'[20120702 Overview Health and Social Care Market.xls]Current Compliance'!$A$13:$A$17739,0)))
Any help much appreciated
Bookmarks