What is the best way to pull and seperate data from a table? VLOOKUP? Anything better?

Question

I have a table that shows a sports league's schedule and results. I want to pull data from that table for each team and then separate it between home and away for each team on separate sheets. What would be the best way to go about this? I've done VLOOKUP, but just wanted to know if there was a better way.

hansvogelaar · Answer

ABlea010&nbsp;
If you have Microsoft 365 or Office 2021, you can use the FILTER function&nbsp;

peterbartholomew1 · Answer

ABlea010&nbsp;Who knows.&nbsp; It all depends on how the data is held and what version of Excel you use.&nbsp; For me VLOOKUP is obsolete so I couldn't imagine using it.&nbsp; Starting with some thing I am pretty sure you don't want, the formula&nbsp;= LET(
    header,     {"Opponent","Goals for","Goals against"},
    homeMatches, FILTER(CHOOSECOLS(results, 4,2,3), Home=team),
    awayMatches, FILTER(CHOOSECOLS(results, 1,3,2), Away=team),
    blankRow,    EXPAND("",2,3,""),
    VSTACK(
      {"Home","",""}, 
      header, 
      homeMatches, 
      blankRow, 
      {"Away","",""}, 
      header, 
      awayMatches
    )
  )&nbsp;entered into a single cell, turns&nbsp;

peiyezhu · Answer

https://techcommunity.microsoft.com/t5/excel/how-to-wrap-table-not-vector-wrapcols-for-tables/td-p/4035406like this?

ablea010 · Answer

FILTER worked perfectly, thank you!

Forum Discussion

What is the best way to pull and seperate data from a table? VLOOKUP? Anything better?

4 Replies

Resources