Search code examples
google-sheetsarray-formulasgs-vlookupgoogle-sheets-formula

Import data via Importxml only from a specific team

This site delivers the data as follows:

enter image description here

I managed to import the two columns I need but it imports from all teams:

=ArrayFormula(IFERROR(HLOOKUP(1,{1;IMPORTXML(A2,A3)},(ROW($A$1:$A400)+1)*2-TRANSPOSE(sort(ROW($A$1:$A$2)+0,1,0)))))

enter image description here

Actually I wish that in cell A1 I could write the team name like for example: Aston Villa. And the import would only pull the names and expected returns from the players of that particular team. Like for example:

enter image description here

This extra detail when filtering importxml I could not create, I would like help with that. I leave here the link of the spreadsheet used as an example:

https://docs.google.com/spreadsheets/d/1krZm-V1geMkz4PeD35GBhiMHdG5kMHnEx-g0iG6oxes/edit?usp=sharing

Solution

  • you can try like this:

    =ARRAYFORMULA(QUERY(IMPORTHTML(A2,"table", VLOOKUP(A1, {
 IMPORTXML(A2, "//h3[@class='injuries-title']"), ROW(INDIRECT("A1:A"&COUNTA(
 IMPORTXML(A2, "//h3[@class='injuries-title']"))))}, 2, 0)), 
 "select Col2,Col4 offset 1", 0))

    0