excel - Report generation based on multi lookup and dynamic columns -

i little stuck report trying generate in excel , hoping help. here summary of trying do:

• table 1 has 1 column called people (it’s list of employees)
• table 2 has 1 column called countries (it’s list of relevant countries)
• table 3 has 3 columns called person, country , date.

there 1 entry every person each time review country.

so data like:

person      |  country         |    date john        |   uk             |    10/01/2013 paul        |   uk             |    15/01/2013 john        |   france         |    15/01/2013 bob         |   spain          |    16/01/2013

the report need produce 1 shows has/hasn't checked each country. columns ‘person’, uk, france, spain (and other unique value country table).

there 1 single row each person yes/no value in relevant column if person has reviewed country i.e. table 3 contains value matches value person , country.

so clear report should similar to:

person      |  uk       |  france   |  spain    john        |   yes     |   yes     |   no paul        |   yes     |   no      |   no bob         |   no      |   no      |   yes

in summary can split 2 problems:

1. how generate table has column every unique value in table (country in explanation above)

2. how double lookup i.e. if exists in table 3 ‘person’=john & ‘country’=uk return ‘yes’, otherwise return ‘no’

i’m happy keep in excel or make use of sql reporting i.e move data sql first.

it's kind of wonky formula =sumproduct() dual lookup.

=if(sumproduct(--($k$2:$k$5=$k13), --($l$2:$l$5=m$10)),"yes","no")

the person/country/date table located in range k1:m5 results table located in range k10:n13. had workbook open , put in corner. (nobody puts sumproduct in corner)

the gist is, -- turns true , false 1 or 0. sumproduct multiply 2 results line line. if both true, 1 x 1 , funnels if yes , no. you'll have mindful of th $ in formula.