Sheet1
| Name | Item_ID | Output |
|---|---|---|
| Name1 | ID1 | some data from Sheet2 |
| Name2 | ID2 | some data from Sheet2 |
| Name3 | ID3 | some data from Sheet2 |
| Name4 | ID4 | some data from Sheet2 |
Sheet2
| Name | ColumnData1 | ColumnData2 | ColumnData3 | ColumnData4 |
|---|---|---|---|---|
| Name1 | data | no match | no match | no match |
| Name2 | no match | data | no match | no match |
| Name3 | no match | no match | no match | no match |
| Name4 | no match | no match | no match | data |
I have two sheets where the primary ID is "Name". In the second sheet there are a series of columns that correspond to the Name in the row. There will be data in either no columns or one column for each Name. There rest of the cells in the row will say "no match".
I need to do an xlookup or similar such that I am pulling data from only the column in Sheet2 that has "data" for a give row into the Output column in Sheet1 for each name. If all columns are "no match" then that is the result that should be returned in the output.
I have gotten the function started with matching the Name but not sure how to get the column data appropriately:
=XLOOKUP(A2,'Sheet2'!$A$1:$A$5000,'Sheet2'!$A$1:$A$5000,"no match",,)
CodePudding user response：
Use the return of the XLOOKUP in FILTER:
=LET(otp,XLOOKUP(A2,'Sheet2'!$A$1:$A$5000,'Sheet2'!$B$1:$E$5000,"no match",,),
FILTER(otp,otp<>"no match","no match"))
Sheet 2 for reference:
