Hi guys,
Just wondering why there's no big arse thread related to excel queries. I'm no programming expert, so I'm counting on excel to get me through making my first model. I'm guessing a lot of others are in the same boat.
Here's to kick it off:
I've got "HomeTeam" and "AwayTeam" columns in column C and D respectively. I want to extract score totals in the "HomeTotal" or "AwayTotal" in column N or Q.
Essentially what I want to do in plain English is this:
1. Check "HomeTeam" column cells for "Team A".
2. If "Team A" is in cell, extract score from column N (the "HomeTotal" column)
3. Scan through the entire column for "Team A" instances and list contiguously (no gaps; I don't want to drag down 500 rows to find the instances).
I guess I would then rinse and repeat with "Team A" but for the away values also.
How do I do this? I've just paid and gone through Wayne Winston's (the author of Mathletics) 20 hour excel course at HBS online, but doing this for real is a quantum leap from the examples I was learning on.
Is this some kind of VLOOKUP task (made more complicated by the fact that the relevant columns for an array are non-contiguous), or an INDEX and MATCH task? I've fiddled around and I just can't figure it out.