Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Release 20160806 MATCH function
The MATCH function accepts multidimensional 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 2dimensional 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 multidimensional ranges.
Maybe it will come to me.
Craig
Comments

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

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

Stephanie,
The example just returns a number.
Can you share the sheet with me and I can try to help.
jcwill23@gmail.com
Craig

I can give one use for a 2d lookup range, but we'll be superinterested 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 2d lookup you can have a 3column 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

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 
For Stephanie's comment / request for help, see this comment:
https://community.smartsheet.com/discussion/matchformula0#comment12383
Craig

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