IF within an INDEX/MATCH?

[RockandGrohl]

My formula has magically stopped working.. it used to look like this:

=IFERROR(IF(COUNTIF(BookData!O:O,EnqData!Q3)=0,"",INDEX(BookData!B:B,MATCH(Q3,IF(BookData!H:H>=C3,BookData!O:O,""),0),0)),"")


What this does:

First portion - Counts whether the unique customer reference in Q3 matches any references in BookData!O:O. If there are no matching references, the formula terminates.

Second portion - Index Match. This returns the Booking reference (held in B:B) based on a match of Q3 in O:O of Booking Data. However, there is an IF statement inside which says "If the Booking Date (H:H) is greater than or equal to C3 (the enquiry date) then return the match in O:O, otherwise, do nothing.

This formula was working nicely, but after some iterations to my sheets I've noticed it's stopped. I have double checked and ensured that each sheet has the correct references. If I remove the IF statement, the formula works as it should, except now it shows me any booking dates that match the reference, not just ones made after the enquiry (so it's rendered useless for calculating how long it takes customers to book after enquiring)

Thank you!

O:O - Booking Data Customer Reference
B:B - Booking Data Booking Reference
H:H - Booking Data Book Date


Q:Q - Enquiry Data Customer Reference
C:C - Enquiry Data Enquiry Date



I hope this makes sense.

 

[RockandGrohl]

Interestingly, my sheet is 190,000 rows deep. The formula appears to work just fine up until row 81,800, where it then stops giving me a result until I remove the "IF" part of the formula which is embedded in the MATCH... Any ideas?

 

[RockandGrohl]

Here's another clue, the Booking Data sheet is approximately 81,800 rows long, the formula appears to stop working on the enquiry sheet about 81,800 rows in... Are these linked?

 

[RockandGrohl]

I've altered the formula so that every time it references the Enquiry sheet it has an absolute enquiry reference in, such as below:

=IFERROR(IF(COUNTIF(BookData!O:O,EnqData!Q84578)=0,"",INDEX(BookData!B:B,MATCH(EnqData!Q84578,IF(BookData!H:H>=EnqData!C84578,BookData!O:O,""),0),0)),"")

This is still giving me no luck.

I repeat, this used to work, I still have the old values copy-pasted1-2-3 in from where it was working before, I have no idea why this has stopped working.

 

[ukmikeb]

Could you put the second IF outside the INDEX, something like :-

[LEFT][COLOR=#333333][FONT=Verdana][LEFT][COLOR=#333333][FONT=Verdana][LEFT][COLOR=#333333][FONT=Verdana]IF(BookData!H:H>=EnqData!C84578,BookData!O:O,[/FONT][/COLOR][/LEFT]INDEX(BookData!B:B,MATCH(EnqData!Q84578,BookData!O:O,0),"")[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][/LEFT]

hth

 

[RockandGrohl]

Could you put the second IF outside the INDEX, something like :-

[LEFT][COLOR=#333333][FONT=Verdana][LEFT][COLOR=#333333][FONT=Verdana][LEFT][COLOR=#333333][FONT=Verdana]IF(BookData!H:H>=EnqData!C84578,BookData!O:O,[/FONT][/COLOR][/LEFT]
INDEX(BookData!B:B,MATCH(EnqData!Q84578,BookData!O:O,0),"")[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][/LEFT]

hth

You know what I suppose that would work - I've had to move on to another project in the short term but I'll be back with a vengeance this afternoon to try that. Thank you!

 

[ukmikeb]

Perhaps this :-

=IFERROR(IF(AND(COUNTIF(BookData!O:O,EnqData!Q3)>0,BookData!H:H>=C3),INDEX(BookData!B:B,MATCH(Q3,BookData!O:O,0)),""),"")

for the complete formula.

hth

 

[RockandGrohl]

In case anyone stumbles on this thread... I missed that it needs to be an array formula. This formula was being pasted in then scrubbed as values via VBA and I had it set as Cells (ActiveCell.Row, "R").Formula = "blahblahblah" when it should've been FormulaArray = "blahblahblah"

Somehow, maybe I accidentally deleted it while mucking around, but Array went missing and it caused a blank formula to be overwritten instead.