Index / Match Formula?

7.80K viewsFormulas and Functions

I am trying to replicate the Excel functionality of using an INDEX / MATCH formula.

I have a Quantrix matrix with rows, columns, and data in the middle (three categories). I want to look up a data value based on two different criteria, one from the row and one from the column.

In Excel I could accomplish this using an INDEX / MATCH. I would use two MATCH formulas to find the row and column that satisfy my criteria, then use INDEX to pinpoint the intersection of that row and column.

Quantrix has the MATCH formula but not INDEX. Any ideas on how this could be accomplished?


Great solution. That will work for any user-input value.

I am also considering the same situation for calculated values. For instance, rather than the user typing the inputs (which would be constrained to the category list), the input would be a calculation from a different matrix. Using constrained inputs wouldn’t work in that situation, but your solution gave me another idea.

I can set up a separate SELECTGREATERTHAN formula that finds the nearest match in the category list, then run the lookup formula from that. This adds two more formulas to the model but allows unlimited flexibility in the inputs.

It took me a minute to figure out how to do a SELECTGREATERTHAN on a category, since Quantrix apparently stores the item names as strings rather than numeric values; so I had to use VALUE(@categoryname) inside my select function to convert the item names into values so that they would be compatible with my inputs (which are numbers).

Attached is my final solution. Thanks for helping me work through this.

You are viewing 1 out of 8 answers, click here to view all answers.

Latest Questions