Release 2016-08-06 MATCH function

J. Craig Williams

The MATCH function accepts multi-dimensional ranges.

 

I believe that EXCEL does not, but can not confirm.

 

This example returns "9".

 

If the range is three columns, match returns 7.

Is there some intended usage of a 2-dimensional search that would be useful?

I see the array is processed as row major.

 

Knowing the size of the table, I can use the returned value to determiine row,column but that seems like a waste of processing power.

 

But I am having difficulty seeing a usage for allowing multi-dimensional ranges.

Maybe it will come to me.

 

Craig

 

 

 

J. Craig Williams

OpenOffice Calc limits lookup_array to a single dimensional array.

 

Excel's Help documentation does not specifically say so, but all examples I found are single dimensional.

 

Craig

 

Stephanie Taylor

Craig, 

 

Help! How do you do this?

 

I can use the returned value to determiine row,column but that seems like a waste of processing power.

 

Thanks, 

Stephanie

J. Craig Williams

Stephanie, 

 

The example just returns a number.

 

Can you share the sheet with me and I can try to help.

 

jcwill23@gmail.com

 

Craig

Erik Rucker

I can give one use for a 2-d lookup range, but we'll be super-interested to see what the community at large does with it!  The example is to imagine a situation where you've got new & old identifiers that map to the same value (say new & old part numbers both map to one price).  With a 2-d lookup you can have a 3-column table that shows NewPart, OldPart, Price and we'll find the price when either the new or old part number is entered.  And, if the price changes, you only need to update it in one place. 

 

Cheers,

Erik

J. Craig Williams

That's a great example Erik, but how does knowing the index value (which is the arrray number after put into a single dimension) help here?

If NewPart is entered, the index for the Price to 2 away, if OldPart, it is only one.

Better would be to just use the INDEX(val,MATCH,MATCH) functionality for the 2 pairs separately. Or LOOKUP for the whoe thing but looking at the two part columns separately.

 

I still don't get it.

Craig 

J. Craig Williams

For Stephanie's comment / request for help, see this comment:

 

https://community.smartsheet.com/discussion/match-formula-0#comment-12383

 

Craig

Erik Rucker

Craig, I agree that knowing the index isn't nearly as useful as knowing the corresponding value.  The example I actually tested used lookup and worked like this:

 1 - I created a sheet with 3 columns:  OldPart, NewPart, Price.  Then added some data to look like this

      OldPart         NewPart         Price

           a                     b                    1

           c                     d                    2

           e                     f                     3

 

2 - then in another column, added a thing to look up and typed "a" in a cell (call it Lookup 1).

3 - finally, I used a lookup formula:  =lookup(Lookup 1, OldPart 1 : Price 3, 3)

 

Then, if the value of Lookup 1 matches either the OldPart or NewPart columns, return the value of the 3rd column in the range (i.e. the Price colum).  Result is that it returns 1 or either a or b, 2 for c or d, and so on.  As you note, you could do something similar by getting the index of the returned value from Match, but you'd need to know the width & height of the lookup range to know what row that corresponded to.  Possible, but much more work!  I don't have an example at hand for when you'd need to do it that way rather than using lookup.

 

Cheers,

Erik