Index match with multiple criteria

**gko_87** · 10-31-2016, 05:33 AM

In the workbook attached, there are three tables (Data, Upcoming Matches and Expected Results tables). What would be the formula that will be that will pull up the respective info from the ‘Data’ table to the ‘Upcoming Matches’ table?

For example, if Man city is playing against Leicester and Man city is the home team while Leicester is the Away team;

1. Index ‘H’ columns in the respective columns to pull up data for Man City
2. Index ‘A’ columns in the respective to pull up data for Leicester

And if Arsenal is playing against on neutral ground (neither team is playing at home), the ‘Ovrl’ columns are to be indexed for both teams.
The expected results for the first three matches are in the ‘Expected Results’ table which I managed to fill using formula but what I cannot figure out is how to factor in the venues to the formula so that they can autofill the rest of the data downwards.

Thanking you all in advance.

Double Condition Index.xlsx

**JohnTopley** · 10-31-2016, 05:44 AM

In E31

=IF($D31="Neutral",VLOOKUP($B31,$B$6:$AI$25,4,0),VLOOKUP($B31,$B$6:$AI$25,2,0))

in F31

=IF($D31="Neutral",VLOOKUP($B31,$B$6:$AI$25,4,0),VLOOKUP($B31,$B$6:$AI$25,3,0))

Repeat for all other columns changing the 2 , 3 and 4 to the appropriate columns