Index&Match with multiple condition...is it possible without helper column?

[PP3321]

Hi thank you always in advance for your generous help...

I need to do Index&Match with multiple criteria from another source.
Problem is that I may not be able to add helper column to the source.

Is there any way to do this without helper column...?

*I have attached the end result with helper column...

 

[Khalid NGO]

Hi,

What about simple SUMIFS:

=SUMIFS($F$4:$F$15,$B$4:$B$15,$J$1,$C$4:$C$15,J$3,$D$4:$D$15,$I4)

Regards,

 

[PP3321]

@Khalid NGO ah I have not thought of sumifs! Thank you so much! I did not know that sumifs work with external sheet!

 

[Khalid NGO]

Or INDEX/MATCH version:

=INDEX($F$4:$F$15,MATCH($J$1&J$3&$I4,INDEX($B$4:$B$15&$C$4:$C$15&$D$4:$D$15,),0))

Regards,

 

[PP3321]

@Khalid NGO wow I have never seen Index&Match&Index...let me study this formula...thank you so much!!!
Which of them would you choose personally?

 

[Khalid NGO]

Welcome dear, keep visiting keep learning...

Thanks to you too, your Thread Title is good, crystal clear explanation for your query and you have included a sample excel file.

Blessing

 

[Khalid NGO]

Which of them would you choose personally?

I love INDEX/MATCH but here (game of numbers only), I will go with SUMIFS, as it is easy to use / edit, and much faster than multiple index/match.

Regards,

 

[PP3321]

@Khalid NGO

Thank you...I went to eat chocolate because working with formulas makes my head go crazy...

2 Points from me.

1. OK I understand the formula now. If row is empty for index, then it means all the rows...right?
INDEX($B$4:$B$15&$C$4:$C$15&$D$4:$D$15,)

2. After a while, I have to hand this over to my colleague.
For easier maintenance reason, I will go for sumifs.
Even though I would like to show off what I learned today from you about index&match&index!!

 

[Khalid NGO]

Hello,

1. Purpose of using additional INDEX function here is to avoid CSE.
You can remove the 2nd INDEX function, like:

=INDEX($F$4:$F$15,MATCH($J$1&J$3&$I4,$B$4:$B$15&$C$4:$C$15&$D$4:$D$15,0))
{Array formula} to be entered with CSE

There could be more ways and thats the Beauty of Excel.

Regards,

 

[bosco_yip]

Here is a checklist of around 32 functions can be used for your reference :



Regards
Bosco

 

[PP3321]

@Khalid NGO thank you. Index can handle arrays, so we do not have to Control + Shift + Enter...Excellent!

 

[PP3321]

@bosco_yip wow...this is amazing...thank you so much for sharing!!!!!!!
I ended-up using sumifs, but will use your chart when coming up with similar task!!

 

[David Evans]

Here is a checklist of around 32 functions can be used for your reference :

View attachment 33573

Regards
Bosco

Thanks Bosco - Useful cheat sheet.

 

[PP3321]

@Khalid NGO @bosco_yip
I encountered another problem...if you do not mind could you please help me?

When I do sumifs with external reference in closed workbook, it returns error.
Index&Match works fine.

Where can I search for functions that work with closed workbook...? I googled 'functions external reference closed workbook' but I could not find much answer...

 

[xlstime]

Please use sumproduct function

@Khalid NGO @bosco_yip
I encountered another problem...if you do not mind could you please help me?

When I do sumifs with external reference in closed workbook, it returns error.
Index&Match works fine.

Where can I search for functions that work with closed workbook...? I googled 'functions external reference closed workbook' but I could not find much answer...

 

[Khalid NGO]

Hi,

SUMIF(s)
COUNTIF(s)
OFFSET
INDIRECT
COUNTBLANK
AVERAGEIF(s)
...

Will not work on closed workbooks. See the link for some info:
https://support.microsoft.com/en-us/kb/260415

Try SUMPRODUCT instead as mentioned by xlstime.

Regards,

 

[PP3321]

@Khalid NGO
Thank you very much!!!!!!