IF statement troubleshooting

**MARKSTRO** · 12-13-2012, 12:54 PM

I have the following IF statement {=IF($R$11:$R$119=$Q2,$K$11:$K$119,1)}

that I would like to use to find the date in column K that corresponds with the match of Q2 in the R11:R119 range.

There may be / are multiple dates that match, I would like to isolate the latest / newest date if possible.

As the formula is now, I get a return of 1/1/1900 except for one and that is 6-24-2011, is this caused by the multiple matches?

Thanks in advance.

**tigeravatar** · 12-13-2012, 12:57 PM

MARKSTRO,

Why are you using an IF() function for this, and an array formula to boot?? This would be best accomplished with an Index/Match:

Please Login or Register  to view this content.

Note that that is a regular formula, no array formula necessary

**MARKSTRO** · 12-14-2012, 11:08 AM

Awesome!!! I can see many more uses for this. I was attempting to modify a sumif statement to get what I wanted. My next level in Excel is to learn when to use Index, Match, and others. Thanks so much.

**MARKSTRO** · 12-14-2012, 11:10 AM

Can you suggest a good source to learn arrays vs. regular formulas etc.

**Ace_XL** · 12-14-2012, 03:37 PM

Always try looking for a non-array solution first, since in most cases it would be less resource intensive (faster calculation).

A good read on array formulas can be found here
http://www.cpearson.com/excel/arrayformulas.aspx