XLOOKUP function for dynamic column header index in a table on Numbers

To find the value at an intersecting point, you need to use MATCH to find the row number and another MATCH to find the column number then use INDEX or OFFSET to get the value at the intersecting point.

Formulas in Table 2:

C2 = MATCH(A2,Table 1::A,0)

D2 = MATCH(B2,Table 1::$1:$1,0)

E2 =OFFSET(Table 1::$A$1,C2-1,D2-1)

fill down to other rows

Note that if you use XMATCH instead of MATCH, you will have to make some adjustments to the formula in E. MATCH includes header rows and columns. XMATCH does not. The two functions give different results.

You can combine C-E into one formula vs using 3 columns to do it. I separated it to illustrate how it worked.

There may be another way to do it with the new "array" functions. If I think of one I'll post it, or maybe someone else will.

Here is one way to do it with the new array functionality. I think the MATCH version is more clear about what it is doing and it will grow with the table without needing any manual adjustment to the formula like this one might. So, really, there was little point in me posting this I guess.

=XLOOKUP(A2,Table 1::A,XLOOKUP(B2,Table 1::$1:$1,Table 1::$2:$8))

Where:

A2 has the value we want to find in column Table 1::A

B2 has the value we want to find in row Table 1::$1:$1

Table 1::$2:$8 is all of Table 1 except for the header row(s)

MATCH does a top to bottom search if you are using "exact match", which is what the 0 in the function does. If you have more than one matching item in column A it will return the one closest to the top.

When you say it doesn't find the correct row, what is it "matching" instead?

When you say you "change the name in col A", are you changing the name in Table 1 or Table 2?

If you do not use the "exact match" parameter in MATCH or XMATCH, they will try to return a "close match" if they can. But with "exact match", it has to be an exact match (case insensitive) to the entire search term or the result is an error. An extra space character or anything else different betwen the search term and what you are looking for will cause it to not match.

Iron_Man-2055 wrote:

Numbers sticks spaces randomly at end of lines, sometimes but not always...very confusing not to be able to tell they are there or get some warning that there is a space between your last text and the EOL marker.

It doesn't add anything on its own. If the data was manually typed in, it is as it was typed in. If the data was imported, it imported what it was given. It is not uncommon for imported data to have problems.