SOLVED

Search cell text for maximum reference value

Copper Contributor

Hello,

 

My excel file contains substances (e.g. "A"), which have multiple comma-separated labels in a single cell (e.g. "H311, H331, H207, R22). The labels correspond to 2 tables and each label has a category value.

I am trying to find the highest category value that is found for each substance.

 

See Example file

 

I have no idea if this is possible. Your help is greatly appreciated.

 

I am a MS 365 user on Windows 11.

6 Replies
best response confirmed by Sander1988 (Copper Contributor)
Solution

@Sander1988 

Try below formula. See the attached file.

=LET(x,FILTERXML("<t><s>"&SUBSTITUTE([@Labels],",","</s><s>")&"</s></t>","//s"),MAX(XLOOKUP(x,H_labels[label],H_labels[Category],0,0),XLOOKUP(x,R_labels[Label],R_labels[Category],0,0)))

 

Harun24HR_0-1659515314344.png

 

@Harun24HRThank you for your quick reply! I would have never been able to come up with this solution. One problem though: when the maximum label is category 1, the output is 0 instead of 1.

0 instead of 1.png

 

You do not have R200 in any label of tables. So, how it would be 1? Can you please explain.

@Harun24HR 

Apologies, I meant to say (and type) H200. Even then the problem persists, the formula produces a 0 instead of 1. Do you know a solution?
 
0 instead of 1.png

@Sander1988 

Because you have trailing space after data in first table (Column A). I have removed those. Check the attached file to this comment.

@Harun24HR 

Thank you very much for this great solution!

1 best response

Accepted Solutions
best response confirmed by Sander1988 (Copper Contributor)
Solution

@Sander1988 

Try below formula. See the attached file.

=LET(x,FILTERXML("<t><s>"&SUBSTITUTE([@Labels],",","</s><s>")&"</s></t>","//s"),MAX(XLOOKUP(x,H_labels[label],H_labels[Category],0,0),XLOOKUP(x,R_labels[Label],R_labels[Category],0,0)))

 

Harun24HR_0-1659515314344.png

 

View solution in original post