How to fetch specific data for specific word/phrase into a single cell in Google sheets

1
votes

I have data in columns A:F and unique entries in column H. I'm using the data in column H to filter it's data from the first two columns and return data in the 5th and 6th columns (E and F).

I have tried and got myself a working formula but it's incomplete to get me the data I need. The formula is:

=IFERROR(QUERY(INDEX(SORT(FILTER({E2:E, ROW(A2:A)}, (A2:A=H2)+(B2:B=H2)), 2, 0),,1),,100000))

*The query function is to allow more than 50000 characters in that happens.

Please check out my data in this google sheets file to better understand.

https://docs.google.com/spreadsheets/d/1hGy_bIAzKj8Qq0l2BGu2ya6mrYaD7pmixPQISzunHrI/edit?usp=sharing

1
google-sheets

1 Answers

1
votes

delete E:F and use this in E2:

=ARRAYFORMULA(IF(A2:A="",,"<td class="""&
 IF(C2:C>D2:D, {"win", "loss"}, 
 IF(C2:C<D2:D, {"loss", "win"}, "draw"))&"""></td>"))

then use this and drag down:

=ARRAYFORMULA(QUERY(INDEX(QUERY(SORT({SPLIT(QUERY(
 FLATTEN(IF($A$2:$B="",,$A$2:$B&"×"&$E$2:$F)), 
 "where Col1 is not null"), "×"), SEQUENCE(COUNTA($A$2:$B))}, 3, 0), 
 "where Col1 = '"&H2&"'"),,2),,9^9))

enter image description here