• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index match vs Sumifs

andreabezt

New Member
Hi,

Please help, I've tried researching for two days now and I'm ready to pull my hair out at this point! Hard coding cells I can get it to work, but I don't know how to make it dynamic and I don't even know whether I'm using the correct formula for what I want to do.

I've attached a file with a simulation of my data. I'm very new in Excel for this kind of purpose, I usually work with SQL, Access VBA and VB.

I have a tab of data that I refresh weekly, pulling data from other databases and it contains an unknown number of rows but the columns are always the same. This data is simulated and vastly simplified on the bookdata tab of my example.

On the books tab of my example I have two dropdowns, the firsts is based on a list of all the months of the year. Based on this first dropdown, it searches my data (on the bookdata tab) for all the matching months and then displays all the names that are associated with that month in dropdown two. These two dropdowns are in column B row 2 and 3 on the books tab of my example.

Based on the result of these two dropdowns, I have to display all the rest of the data in the specific row in bookdata, as seen in column G and H of the books tab.

Here's my problem
SUMIFS works swell with numbers, but it won't display text. I've done some reading and am now using Index match for that field (column I of books). As said, hard coding cell numbers works, but I can't make it work dynamically. Can someone please help me with this, even tell me if I'm using the correct formula!

Thank you in advance!

Andrea
 

Attachments

bosco_yip

Excel Ninja
I3 should read as :

=INDEX(bookdata!F:F,MATCH(1, (bookdata!C:C=B3)*(bookdata!A:A=LEFT(B2,3)),0))

This is an array formula, to be confirmed by pressing "Ctrl"+"Shift" + "Enter" 3 keystrokes together.

And,

For the multiplication of conditional checking it is advised to use limited range of cells instead of whole column range (which is 1 million cells)

So,

The formula become :

=INDEX(bookdata!F:F,MATCH(1, (bookdata!C1:C1000=B3)*(bookdata!A1:A1000=LEFT(B2,3)),0))

Remark :

For "Index match vs Sumifs"

Although using Sumifs formula look shorter than Index+Match formula,
in fact Index+Match always faster than Sumifs (Lookup function always faster than Math function).


Regards
 
Last edited:

Peter Bartholomew

Well-Known Member
Using Microsoft 365:
Code:
= XLOOKUP( 1,
    (bookdata[Month]=LEFT(month,3)) * (bookdata[Person Reading]=person),
     bookdata[[Hours spent on Reading]:[Status]] )
or
Code:
= FILTER( bookdata[[Hours spent on Reading]:[Status]],
    (bookdata[Month]=LEFT(month,3)) * (bookdata[Person Reading]=person),
    "No data")
return all three values (time, count and status) with a single formula.

It is possible to treat worksheet formulas as programming if that is your mindset. For me, VBA is a second choice, largely used for automation and event handling.
 

andreabezt

New Member
Thank you so much, I really appreciate your help. As for the VBA, I guess I'm just used to it :) Once I know more about excel I'm sure I'll do better!
 

andreabezt

New Member
Hi bosco-yip,

I've tried this, but I still get a not #N/A :-(, I'm not sure why it won't work for me. If I use VLOOKUP, can I check for 2 matching variables to get the row?

71258

=INDEX(bookdata!F:F,MATCH(1, (bookdata!C1:C1000=B3)*(bookdata!A1:A1000=LEFT(B2,3)),0))
 

andreabezt

New Member
Hi bosco-yip,

I've tried this, but I still get a not #N/A :-(, I'm not sure why it won't work for me. If I use VLOOKUP, can I check for 2 matching variables to get the row?

View attachment 71258
Oh my, I did the ctrl, shift, enter incorrectly, when I highlighted it and repeated, it actually did it and it works!!! Thank you so much! Funny that none of the web tutorials tell you about this, seems to me quite an important step! Thank again, that was a great help!
 
Top