-1

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",,)
asaysno
  • 35
  • 6

1 Answers1

3

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"))

enter image description here

Sheet 2 for reference:

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thank you. So that I understand, what is otp? – asaysno Aug 10 '22 at 17:13
  • `otp` is a variable in which I put the output of the XLOOKUP so I do not need to repeat the XLOOKUP twice in the FILTER. [`LET()`](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999#:~:text=The%20LET%20function%20assigns%20names,through%20Excel's%20native%20formula%20syntax.) allows us to store items in variables for use later in the formula. – Scott Craner Aug 10 '22 at 17:16
  • Unfortunately the data being returned is "0", not the data from the column or "no match". – asaysno Aug 10 '22 at 17:20
  • With your data in Sheet 2, the formula returns what it is supposed to. See edit for picture. So you need to tell me what is different than what you showed in your question. – Scott Craner Aug 10 '22 at 17:23
  • I have nine data columns in sheet 2. That is the only difference other than naming. – asaysno Aug 10 '22 at 17:35
  • Do you have blanks in those columns? Did you adjust the 'Sheet2'!$B$1:$E$5000 to include the extra columns? There are soooooo many things to check. remember I cannot see your data. – Scott Craner Aug 10 '22 at 18:08
  • I can also get your formula to work with a test file with the correct number of columns etc. But when I apply it to my actual data file it returns 0. No blanks in the data file. – asaysno Aug 10 '22 at 18:13
  • Then there is something with the data that is inconsistent with the test data you provided. Again, I cannot help you beyond what I have giving. I cannot see your data. And no I will not download a file from unknown sources. – Scott Craner Aug 10 '22 at 18:16