+ Reply to Thread
Results 1 to 7 of 7

How to get blank cell instead 0 for index match>

  1. 03-24-2022, 05:40 AM #1
    rayted
    rayted is offline
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    How to get blank cell instead 0 for index match>

    Hi guys

    Qyicki question i`m sure..

    I have an index match

    =INDEX('PV client - list from Ops'!$C$4:C$80,MATCH(A3,'PV client - list from Ops'!$A$4:$A$80))

    How do I edit this to say if my index (C4:C80) has 'blank' cell, return a blank cell - don't show 0? tHE VALUEs are just numbers (blank, 1, 2 and 3)

    My formula is in cell C4 on tthe tracker-ops use only tab

    Thanks,

    R.
    Attached Files Attached Files
    Last edited by rayted; 03-24-2022 at 05:52 AM.
    Thanks,

    R.


    Reply With QuoteRegister To Reply
  2. 03-24-2022, 05:53 AM #2
    janmorris
    janmorris is offline
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: How to get blank cell instead 0 for index match>

    normally it would be something like:

    IF(this_formula="","", else_do_formula)
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]
    Reply With QuoteRegister To Reply
  3. 03-24-2022, 05:57 AM #3
    rayted
    rayted is offline
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: How to get blank cell instead 0 for index match>

    hi janmorris

    thank you for your reply. I tried iferror but then lost all the values..
    Reply With QuoteRegister To Reply
  4. 03-24-2022, 05:59 AM #4
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,714

    Re: How to get blank cell instead 0 for index match>

    You could amend your formula in C3 to this:

    =IFERROR(1/(1/INDEX('PV client - list from Ops'!$C$4:$C$80,MATCH(A3,'PV client - list from Ops'!$A$4:$A$80))),"")

    then copy down.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  5. 03-24-2022, 06:06 AM #5
    nick.williams
    nick.williams is offline
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: How to get blank cell instead 0 for index match>

    Hi,

    Firstly, your formula was missing the absolute reference ($) in the red section. This meant you got zeros for a lot of your lower data.

    INDEX('PV client - list from Ops'!C4:C80,MATCH(A3,'PV client - list from Ops'!$A$4:$A$80))

    If you want to hide the 0, a common way is to use "" instead, like this:

    =IF(INDEX('PV client - list from Ops'!$C$4:$C$80,MATCH(A3,'PV client - list from Ops'!$A$4:$A$80))=0,"",INDEX('PV client - list from Ops'!$C$4:$C$80,MATCH(A3,'PV client - list from Ops'!$A$4:$A$80)))

    With Excel 365 you can use the LET function to shorten this (and make it more efficient, as when things are repeated it only calculates once), for example:

    =LET(a,INDEX('PV client - list from Ops'!$C$4:$C$80,MATCH(A3,'PV client - list from Ops'!$A$4:$A$80)),IF(a=0,"",a))
    Reply With QuoteRegister To Reply
  6. 03-24-2022, 06:07 AM #6
    rayted
    rayted is offline
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: How to get blank cell instead 0 for index match>

    Hi Pete

    that helps thanks, I had one cell in my range with text which didn't copy across but I've copied and pasted it.

    thanks!
    Reply With QuoteRegister To Reply
  7. 03-24-2022, 06:13 AM #7
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,714

    Re: How to get blank cell instead 0 for index match>

    Yes, the approach I gave you was for numbers only (which is what you said you had).

    Pete
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] When Index Match is blank, I want it to show a value from another cell
    By Frank Batson in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-30-2021, 11:46 AM
  2. [SOLVED] Return non blank cell using INDEX and MATCH
    By mabbutt in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-11-2019, 08:16 PM
  3. INDEX MATCH to find first non blank cell
    By ELFIJKA in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-21-2019, 05:28 AM
  4. Index/ Match Formula, How to return blank cell as a blank not 0
    By MDResearcher in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-24-2016, 08:40 PM
  5. [SOLVED] how to get blank cell when using index and match
    By sarmadamidi in forum Excel General
    Replies: 9
    Last Post: 12-21-2015, 11:21 AM
  6. Index Match combined with blank cell
    By balabuschka in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-24-2015, 11:05 AM
  7. [SOLVED] Index/Match for first non blank cell
    By Fletch74 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-17-2014, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1