Using Index/Match with an IF Statement???

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
151
Office Version
  1. 2011
Platform
  1. MacOS
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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)
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,244
Messages
6,170,976
Members
452,372
Latest member
Natalie18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top