Results 1 to 4 of 4

Thread: Using Index/Match with an IF Statement???
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2012
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using Index/Match with an IF Statement???

    I don't know if it is possible, but is there a way to use index/match in an IF statement? Or maybe something else will work. I have a range of A5:F250 with a list of events for different entries.

    Column A = First Names
    Column B = Last Names
    Column D = Name of Events (Birthday, Deceased, Etc.)
    Column F = Days Until Next Event

    I would like to place a zero in Column F of a persons birthday row if the first name, last name and the word Deceased are in the same row some where else. Otherwise I will calculate the number of days until the next birthday. I have a formula for that. I just need to get there in an IF statement. Below is a formula I tried but was called volatile by Excel.

    =IFERROR(IF(C5="","",IF(INDEX($C5:$C$250,MATCH(1,(A5=$A$5:$A$250)*(B5=$B$5:$B$250)*("Deceased"=$D$5:$D$250),0))=C5,0,IF(I5>TODAY(),DATEDIF(TODAY(),I5,"YD"),DATEDIF(TODAY(),K5,"YD")))),"N/A")

    Thanks to all for all the help in the past and for any help here.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,408
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Using Index/Match with an IF Statement???

    Try like this

    =IF(COUNTIFS(A$5:A$250,A5,B$5:B$250,B5,D$5:D$250,"Deceased"),0,your days formula here)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular
    Join Date
    Sep 2012
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Index/Match with an IF Statement???

    Thank you, that actually put 0 in both the birthday and deceased rows which makes more sense. I might mention that is the days until column that I am putting it in.
    And thank you for a lesson on COUNTIFS, I see more uses for it now.
    Last edited by anwaee2; Sep 20th, 2019 at 08:43 AM.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,408
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Using Index/Match with an IF Statement???

    You're welcome.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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