Simplify INDEX/MATCH Formula???

Question

Excel 2010 ...

I have below array formula working ... However, I am thinking there might be a way to shorten (or simplify it) ... And quite frankly, I am not a fan of formulas that contain hard-stop numbers like the "5" I am presently using in this Formula ... So I am turning to this board of Excel Champions.

{=IF($B10=I$5,INDEX(WS2!$A$2:$F$30000,MATCH(1,(WS2!$A$2:$A$30000=$C10)*(WS2!$C$2:$C$30000=I$5),0),5),IF($B10=I$7,INDEX(WS2!$A$2:$F$30000,MATCH(1,(WS2!$A$2:$A$30000=$C10)*(WS2!$C$2:$C$30000=I$7),0),5),""))}

Thanks ... Kha

Answer 1

The first thing that I would suggest is named ranges. Having meaningful names in your formula will make it more meaningful when you go back to look at it later.

Answer 2

Ashish ... (Good morning)

Saw your response last night, but waited to see File this am before attempting to provide further details as you suggested ... Started this morning with an empty head (not unusual) & started to layout what I was attempting to do so I could better present to this board ... In doing so, I managed to come up with a better solution I feel more comfortable with.  Instead of using INDEX/MATCH ... I changed to using "MIN(IF" & "MAX(IF" in Col Ranges expecting Date Returns & "SUM(IF" for Col Ranges expecting Numeric Returns ... Most likely not most efficient, but it appears to be working ...

Sample ... Return MIN Date

{=IF(AND($B10<>I$5,$B10<>I$7),"",MIN(IF(OR($B10=I$5,$B10=I$7),IF(WS2!$A:$A=$C10,IF(WS2!$C:$C=$B10,WS2!E:E)))))}

Sample ... Return Numeric Value

{=IF(AND($B10<>I$5,$B10<>I$7),"",SUM(IF(OR($B10=I$5,$B10=I$7),IF(WS2!$A:$A=$C10,IF(WS2!$C:$C=$B10,WS2!$F:$F)))))}

So while you did not directly provide a solution to my post (indirectly, you did ... & for that I Thank You)

Thank you for supporting these boards where so many valuable solutions are found for those of us less intimate with Excel (in particular ... Me) ... Kha

Answer 3

Hi,

You are more likely to get a better answer if you share data and explain the question rather than merely posting your formula.