Index match vs Sumifs (2024)

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

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

Using Microsoft 365:

= XLOOKUP( 1, (bookdata[Month]=LEFT(month,3)) * (bookdata[Person Reading]=person), bookdata[[Hours spent on Reading]:[Status]] )

or

= 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.

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?

bosco_yip said:

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

Click to expand...

andreabezt said:

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

Click to expand...

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!