INDEX/MATCH formula has me stumped

Susan Vieira

I am trying to understand a formula that someone else has written and I cannot get it. It works great but I do not know how and I would like to. It looks like this:

=COUNTIFS(INDEX({EDB Full Range}, 0, MATCH([Equipment / Limit Required]@row, {EDB Full Range}, 0)), >[Equipment Max#]@row, {EDBDate}, >=Start@row, {EDBDate}, <=[Last Date]@row)

I understand all of it EXCPET the 0 as the row_index portion of the INDEX formula. I am stumped as there is no 0 row position. How does the 0 come into play; what is it doing? Does the 0 basically say look at all rows? As opposed to a specific row? Or is it looking at column headers? These are just some theories I was playing around with as I could not find an answer.

Thank you!

Genevieve P.

Hi @Paul Newcome

Yes! I think I've actually been working with @Susan Vieira on this other post, here.

You would use the 0 if you wanted the MATCH to use the column index in an INDEX function instead of looking at the row index for the match. Basically, if you want to evaluate a selection of columns and use the MATCH function to bring back one column from that range.

Most recently I used it like this:

=COUNTIF(INDEX({All Columns}, 0, MATCH("Column 1”, {Other Sheet First Row}, 0)), “No Concern”)

This was for a table where the column names from one sheet were listed as the rows in the metrics table:

Column 1

Column 2

Column 3

And the user wanted a COUNT of the value "No Concern" in each column, but only wanted to write one formula to find the answer for every column (versus writing an individual cross-sheet reference).

The First Row of the other, source sheet had to have the column names pasted into it for the INDEX function to reference and find a match. Then it would pull just that one column out of the {all columns} range to evaluate for the COUNTIFS.

I feel like I didn't explain that very well... let me know if screen captures will help!

Cheers,

Genevieve

Leibel S

@Susan Vieira

I have never seen INDEX used that way, but it definitely works (The 0 makes it return the entire range of rows).

@Paul Newcome Have you ever seen that or used it that way?

Paul Newcome

@Leibel Shuchat I remember trying a zero once quite some time ago, and I ended up getting an error. I assumed it was because there is no "row 0". Maybe I had an error elsewhere or it has changed since then (this was a few years ago when I first learned how to use the INDEX function).

@Susan Vieira What is in the {EDB Full Range}? Is it the entire sheet? What is the purpose of the formula? Exactly what is it counting? How are your sheets laid out? Is the creator of this here in the Community? I'd love to take a look at some of their other solutions.

@Genevieve P. @Andrée Starå @Mike Wilday Have any of you used a 0 as the row number in an INDEX? Are any of you able to break this formula down a bit? I tried to @mention @L@123 as well, but for some reason I can't get that name to load as an option today.

Genevieve P.

Hi @Paul Newcome

Yes! I think I've actually been working with @Susan Vieira on this other post, here.

You would use the 0 if you wanted the MATCH to use the column index in an INDEX function instead of looking at the row index for the match. Basically, if you want to evaluate a selection of columns and use the MATCH function to bring back one column from that range.

Most recently I used it like this:

=COUNTIF(INDEX({All Columns}, 0, MATCH("Column 1”, {Other Sheet First Row}, 0)), “No Concern”)

This was for a table where the column names from one sheet were listed as the rows in the metrics table:

Column 1

Column 2

Column 3

And the user wanted a COUNT of the value "No Concern" in each column, but only wanted to write one formula to find the answer for every column (versus writing an individual cross-sheet reference).

The First Row of the other, source sheet had to have the column names pasted into it for the INDEX function to reference and find a match. Then it would pull just that one column out of the {all columns} range to evaluate for the COUNTIFS.

I feel like I didn't explain that very well... let me know if screen captures will help!

Cheers,

Genevieve

Leibel S

@Paul Newcome

I tested it it and you can actually use it if you are working with charts of data.

You have a cross sheet reference that spans many columns. You can use INDEX to define the range within another function (even within another INDEX function).

Example:

=SUM(INDEX({range multiple columns},0,2))

Example 2:

Pull single point of data from chart (salary pf employee from specific week).

=INDEX(INDEX({range multiple columns of salary}, 0, MATCH(Week@row, {header range}, 0)), MATCH(Employee@row, {Employees column}, 0))

Leibel S

@Randy Saad

This should also solve your issue in the below post:

https://community.smartsheet.com/discussion/74022/is-there-a-way-to-sum-a-collection-from-a-two-dimensional-range

It would be something like:

=SUMIFS(INDEX({range multiple columns of hours}, 0, MATCH(Week@row, {header range}, 0)),{Employees column},Employee@row)

Susan Vieira

@Genevieve P. , yes thanks. Sorry for the double post. I posted this question before I found the original post concerning this very formula by my colleague. Thanks so much for all the help again.

I am going to comb through the rest of these posts to see if I learn more about it. I find the INDEX fx quite challenging so all info is welcome.

Mike Wilday

Wow. Cool find! @Paul Newcome Thanks for tagging me on this post.

Susan Vieira

@Paul Newcome

I believe @Genevieve Evans answered your questions. If you still want to know exactly what I was trying to achieve with my formula let me know. It works great.

Paul Newcome

@Susan Vieira Yes. It all makes sense. And while the INDEX function may be a little bit of a challenge to get the hang of, once you do you will find all kinds of uses for it. The COLLECT function is another one like that. It can be a little tricky to get used to, but it can open up a ton of possibilities.

@Genevieve P. Thanks for the explanation! I think the reason I was getting errors was because I was using single columns based on the way I have read it.

@Mike Wilday Definitely cool!

@Luke Warner You've come up with some pretty interesting solutions. Have you ever used something like this?

I am definitely going to be bookmarking this thread and playing around with it because I can see this really helping out in a lot of other areas as well.

Susan Vieira

I have bookmarked this as well. I did just use INDEX/COLLECT successfully and was pretty happy with it since I try and avoid VLOOKUPs where I can. Although I just used an INDEX/MATCH and a INDEX/COLLECT to obtain the same results (configured differently of course) and was wondering if one was better to use than the other. But I will continue to play with it. Thanks to everyone again.

Paul Newcome

@Susan Vieira INDEX/MATCH can only match on one set of criteria whereas INDEX/COLLECT can match on multiple range/criteria sets. Generally speaking you can use INDEX/COLLECT in place of an INDEX/MATCH if that's what you prefer, but sometimes there are benefits to an INDEX/MATCH.

For instance... If you are looking across a table and want to pull a single cell based on row and column matches, then you can use an INDEX/MATCH/MATCH, but using an INDEX/COLLECT won't work.

You also cannot nest a COLLECT inside of a COLLECT, so if you have to use a COLLECT function to pull the proper value to match on, then you can put the COLLECT inside of the first portion of the MATCH function, but using an INDEX/COLLECT, you would have to pull that collected data to match on into a separate column and reference that.

Susan Vieira

@Paul Newcome that is great info, thanks so much. I will be referencing this thread for sure. As always, you're all so helpful, thanks a bunch.

Paul Newcome

Always happy to help. 👍️