Forum Discussion

JSM078's avatar
JSM078
Copper Contributor
Mar 04, 2024

Index, Match with an IF statement

Please can I get some help. I may be ignoring the obvious...

 

I am looking to add an IF statement to the end of my INDEX, MATCH formula; if the returned value is 0 the cell is left blank.

 

The formula is am currently working from is as below:

=INDEX('27.02.24'!F:F,MATCH(Summary!A60,'27.02.24'!B:B,0))

This is working fine with little frustration that the cell then reports as 0 when it has found the match but there is no clue in the column of indexing.

 

Due to additional formatting applied the returned value of 0 is proving to be an issue, I thought a simple solution would be to add an IF statement that if the returned INDEX,MATCH value is 0 the cell is left blank. 

 

I am however having little luck with what I am assuming is obvious yet I'm too deep into a day of excel I am missing it.

 

Please help...

Excel
Excel on Mac
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Mar 04, 2024

    JSM078 

    If you have Microsoft 365 or Office 2021:

     

    =LET(v, INDEX('27.02.24'!F:F,MATCH(Summary!A60,'27.02.24'!B:B,0)), IF(v="", "", v))

     

    If you have an older version:

     

    =IF(INDEX('27.02.24'!F:F,MATCH(Summary!A60,'27.02.24'!B:B,0))="", "", INDEX('27.02.24'!F:F,MATCH(Summary!A60,'27.02.24'!B:B,0)))

Resources