+ Reply to Thread
Results 1 to 14 of 14

index match with blank entries

Hybrid View

  1. 07-09-2005, 02:39 AM #1
    andrewm
    andrewm is offline
    Forum Contributor
    Join Date
    05-08-2005
    Location
    Australia
    MS-Off Ver
    excel 2019
    Posts
    112

    index match with blank entries

    Hi

    I have a formula - =index(B1:B10,(match(C1,A1:A10,0)))

    however when there is a blank in row B it returns a 0, how can I change it to enter a blank instead?

    many thanks

    andrewm
    Reply With QuoteRegister To Reply
  2. 07-09-2005, 04:12 AM #2
    Krishnakumar
    Krishnakumar is offline
    Forum Contributor
    Join Date
    02-19-2005
    Location
    Gurgaon,India
    MS-Off Ver
    2007,2010,2013
    Posts
    180
    Hi Andrewm,

    Try,

    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:A10,0)))

    HTH
    Kris
    Reply With QuoteRegister To Reply
  3. 07-09-2005, 06:05 AM #3
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    Reply With QuoteRegister To Reply
  4. 09-06-2005, 04:05 AM #4
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    Reply With QuoteRegister To Reply
  5. 09-05-2005, 10:05 PM #5
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    Reply With QuoteRegister To Reply
  6. 09-05-2005, 11:05 PM #6
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    Reply With QuoteRegister To Reply
  7. 09-06-2005, 12:05 AM #7
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    Reply With QuoteRegister To Reply
  8. 09-06-2005, 06:05 AM #8
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    Reply With QuoteRegister To Reply
  9. 09-06-2005, 01:05 AM #9
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    Reply With QuoteRegister To Reply
  10. 09-06-2005, 07:05 AM #10
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    Reply With QuoteRegister To Reply
  11. 09-06-2005, 02:05 AM #11
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    Reply With QuoteRegister To Reply
  12. 09-06-2005, 10:05 AM #12
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    Reply With QuoteRegister To Reply
  13. 09-06-2005, 12:05 PM #13
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    Reply With QuoteRegister To Reply
  14. 09-06-2005, 03:05 AM #14
    Bob Phillips
    Guest

    Re: index match with blank entries

    TYpo

    =IF(ISBLANK(INDEX(B1:B10,(MATCH(C1,A1:A10,0)))),"",INDEX(B1:B10,(MATCH(C1,A1
    :A10,0))))

    --
    HTH

    Bob Phillips

    "Krishnakumar" <Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com>
    wrote in message
    news:Krishnakumar.1rwaaa_1120899907.4138@excelforum-nospam.com...
    >
    > Hi Andrewm,
    >
    > Try,
    >
    >
    =if(isblank(index(B1:B10,(match(C1,A1:A10,0))),"",index(B1:B10,(match(C1,A1:
    A10,0)))
    >
    > HTH
    >
    >
    > --
    > Krishnakumar
    >
    >
    > ------------------------------------------------------------------------
    > Krishnakumar's Profile:
    http://www.excelforum.com/member.php...o&userid=20138
    > View this thread: http://www.excelforum.com/showthread...hreadid=385815
    >


    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)

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