Hello.
I can't figure out how to search in multiple ROWS and COLUMNS and then return cell from another column.
XLOOKUP($A2;Another table::$E5:$AZ139;Another table::$C;"—";0;1) - gives an error - "Argument 3 of XLOOKUP is invalid."
How to solve it? Is it even possible?
(don't mind punctuations mark, it's different country set)
Thank you for response.
Mac mini, macOS 12.3
For as many columns that you want to search I can't offhand think of a way to do it except for one or a small handful of lookups. If it was just a few columns you could concatenate them into one new column and search on that column but you won't want to concatenate columns E through AZ. The first example below uses a column to search each row then you do a lookup on that new column. The second example concatenates columns.
Formula in Table 1 cell D2 =XMATCH(Table 2::B$2;A2:C2;0)>0
fill down to complete the column
Formula in Table 2 cell B2 = XLOOKUP(TRUE,Table 1::D;Table 1::E;"na";0)
Hide column D of Table 1 when it is all set up.
Here is using concatenation:
Formula in Table 1 D2 ="-"&A2&"-"&B2&"-"&C2&"-"
fill down
Formula in Table 2 B2 =XLOOKUP(REGEX("-"&B2&"-"),Table 1::D,Table 1::E,"not found",2)
There may be better choices/combinations of formulas than the ones I used, or even a better way altogether. I don't think there is a way to do it all in one cell, though. I think it will any solution will need a column. But maybe someone else will figure out a way.
For as many columns that you want to search I can't offhand think of a way to do it except for one or a small handful of lookups. If it was just a few columns you could concatenate them into one new column and search on that column but you won't want to concatenate columns E through AZ. The first example below uses a column to search each row then you do a lookup on that new column. The second example concatenates columns.
Formula in Table 1 cell D2 =XMATCH(Table 2::B$2;A2:C2;0)>0
fill down to complete the column
Formula in Table 2 cell B2 = XLOOKUP(TRUE,Table 1::D;Table 1::E;"na";0)
Hide column D of Table 1 when it is all set up.
Here is using concatenation:
Formula in Table 1 D2 ="-"&A2&"-"&B2&"-"&C2&"-"
fill down
Formula in Table 2 B2 =XLOOKUP(REGEX("-"&B2&"-"),Table 1::D,Table 1::E,"not found",2)
There may be better choices/combinations of formulas than the ones I used, or even a better way altogether. I don't think there is a way to do it all in one cell, though. I think it will any solution will need a column. But maybe someone else will figure out a way.
I just solved it this way:
IFS(IFERROR(XMATCH($A4;Anothertable::5:5;0);0);Anothertable::$C5;IFERROR(XMATCH($A4;Anothertable::6:6;0);0);Anothertable::$C6;IFERROR(XMATCH($A4;Anothertable::7:7;0);0);Anothertable::$C7;) etc for each row.
Solved in single cell))
but, I’d really like to see others solutions.
While XLOOKUP($A4;Another table::$E;Another table::$C;"—";0;1) works perfectly.
Thank you a LOT for your expanded reply. I will try your ideas.
Hi! I really hope you will be able to pay attention for the question here Numbers: Function that returns many relia… - Apple Community
Thank You!
XLOOKUP: searching multiple columns
