Return column values from the column title

user_11462274 0
Viewed 49

In answer to my question (I will leave the link below), the formula being used and the result are as follows:

https://stackoverflow.com/a/65451225/11462274

=IMPORTXML("https://www.soccerstats247.com/predictions/brazil/serie-a/",
           "//table[@id='soccerContentPlaceHolder_gv1X2Predictions']/tbody/tr")

enter image description here

The columns can change position, but the title of each one follows the new column position, so how could I ask for a formula to search where the column with title X is and from that find the values of ⚽ Matches

In this case, the value would be: Explanation via VLOOKUP:

=ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(B2:B),B1:F,3,FALSE)))

enter image description here

The problem with VLOOKUP is that the column with the title X may not always be in column D and ⚽ Matches may not always be in column B

google-sheets-formula google-sheets
1 Answers

use:

 =ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(B2:B), 
  {FLATTEN(QUERY(TO_TEXT(TRANSPOSE(IMPORTXML(
  "https://www.soccerstats247.com/predictions/brazil/serie-a/",
  "//table[@id='soccerContentPlaceHolder_gv1X2Predictions']/tbody/tr"))), 
  "where Col1 contains 'Matches'", 0)), IMPORTXML(
  "https://www.soccerstats247.com/predictions/brazil/serie-a/",
  "//table[@id='soccerContentPlaceHolder_gv1X2Predictions']/tbody/tr")}, 5, 0)))

eg. the part is:

=INDEX(FLATTEN(QUERY(TO_TEXT(TRANSPOSE(IMPORTXML(
 "https://www.soccerstats247.com/predictions/brazil/serie-a/",
 "//table[@id='soccerContentPlaceHolder_gv1X2Predictions']/tbody/tr"))), 
 "where Col1 contains 'Matches'", 0))

and the full formula:

 =ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(B2:B), 
  {FLATTEN(QUERY(TO_TEXT(TRANSPOSE(IMPORTXML(
  "https://www.soccerstats247.com/predictions/brazil/serie-a/",
  "//table[@id='soccerContentPlaceHolder_gv1X2Predictions']/tbody/tr"))), 
  "where Col1 contains 'Matches'", 0)), IMPORTXML(
  "https://www.soccerstats247.com/predictions/brazil/serie-a/",
  "//table[@id='soccerContentPlaceHolder_gv1X2Predictions']/tbody/tr")}, 
  MATCH("X", INDEX(IMPORTXML(
  "https://www.soccerstats247.com/predictions/brazil/serie-a/",
  "//table[@id='soccerContentPlaceHolder_gv1X2Predictions']/tbody/tr"), 1), 0)+1, 0)))
user_56326290
Related