I have the following table

enter image description here

And I would like a formula that would produce the following result

enter image description here

I would like to iterate through the table going from left to right top to bottom and if there is a value in a cell, I would like to return with the matching top row criteria. How would I do this?

  • Welcome. Please add a brief description of your search/research efforts as is suggested on How to Ask.
    – Rubén
    Jul 13, 2019 at 0:47

1 Answer 1


 IF(C2:O<>"", "♦"&C2:O&"♠"&C1:O1, )),,999^99)),,999^99), "♦")), "♠")))


  • That's quite a formula. Your answer would benefit a lot if you could explain each part of the formula. Jul 14, 2019 at 18:38
  • @VidarS.Ramdal if C2:O isn't empty each value of that range is combined with whats in row 1 eg header and two unique symbols resulting into: ♦1♠XXS for C2. then all is smashed in one single cell with QUERY(TRANSPOSE(QUERY(TRANSPOSE( ),,999^99)),,999^99). once it's all in one cell the string is split by first unique symbol columns are transposed to rows and again split by 2nd unique symbol . as a final touch, the TRIM is used to fix for possible spaces after each value.
    – user0
    Jul 14, 2019 at 19:06
  • Great - you should edit that into your answer. Comments are temporary. Jul 14, 2019 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.