Numbers: Match-Related Question

Question

Level 1

8 points

Numbers: Match-Related Question

On one sheet of a Numbers spreadsheet I have the following table to be referenced from other sheets in match functions:

On another sheet I am attempting to match the combination of two cells — the "+" and "-" signs in A + (C or I or O) — to the column headers in the table, then match it against the numerical values in the "ROOT" columns (the results in purple are the values that should be returned from the match):

The basic match function is no problem, but it's the combining of the "+" and/or "-" signs as a part of the formula that has me stumped.

What function(s) do I need to add to the match function in my equations?

Posted on May 15, 2023 9:42 AM

Reply

Answer 1

judddanby Author

Level 1

8 points

May 16, 2023 6:59 AM in response to Badunit

"XMATCH($A2&"/"&C2…" was all I needed to accomplish the match correctly. Thanks, Badunit! (Glad my description made enough sense.)

As for your entire response, I'll spend some time learning why I'd use XMATCH instead of my MATCH, and learn about OFFSET.

Reply

Answer 2

judddanby Author

Level 1

8 points

May 15, 2023 10:47 AM in response to judddanby

I thought of a "cheap" solution: I created new (hidden) columns that concatenate the "+" and "-" signs into the strings ("+/+," "+/-," etc.) used in the table, then use those cells for the INDEX/MATCH function.

Still, I'd love to learn if there's a more sophisticated way to do the same thing, one that doesn't involve creating extra columns.

Reply

Answer 3

Badunit

Level 7

31,268 points

May 15, 2023 11:00 AM in response to judddanby

I'm not exactly sure what you are doing. I am assuming that in cell E2 of your lower table you want the result from the 2-Triad Chords table that is at the intersection of "+/-" and "4". That would be cell D6 with the value "4-20_(L)".

=OFFSET('2-Triad Chords'::$A$1,D2+1,XMATCH($A2&"/"&C2,'2-Triad Chords'::$1:$1))

XMATCH was needed to find the +/- column but the row can be calculated with math.

I think you can copy/paste that formula to the other cells that need it

Reply

Answer 4

Badunit

Level 7

31,268 points

May 16, 2023 10:07 AM in response to judddanby

XMATCH lets you pick the direction to search. That feature didn't get used here. And there is an important difference in the searching. MATCH includes header rows/columns and footers in its search. XMATCH does not, unless you are searching only in a header row/column or footer row like you are here. Even then, though, if you are searching a header row it will not include any header columns (and vice versa). In your table, it started searching at column B. MATCH would have started at column A. With MATCH you would have had to subtract 1 to get the same result that XMATCH gave.

MATCH is good for giving you the exact row number or column number of a match, regardless of how many header rows/columns the table has, as long as whatever is in the headers doesn't affect the results.

XMATCH is better if you need to use exclude the headers in the search but still use "entire column" or "entire row" addressing.

Reply