Aug 03 2022 01:13 AM
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.
Aug 03 2022 01:28 AM
SolutionTry 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)))
Aug 03 2022 03:13 AM
@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.
Aug 03 2022 03:17 AM
Aug 04 2022 03:42 AM
Aug 04 2022 03:51 AM
Because you have trailing space after data in first table (Column A). I have removed those. Check the attached file to this comment.
Aug 04 2022 04:04 AM
Thank you very much for this great solution!
Aug 03 2022 01:28 AM
SolutionTry 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)))