Excel - VLOOKUP to create new table

Question

I have a given table B1 to H9 and I'm trying to create the output in below image:

Link2Image

Input:

+-----------+-----------+------+---------+------------+--------+-----------------------------+
| ArticleID |   Link    | Year | Quarter | QuestionID | Answer |           Comment           |
+-----------+-----------+------+---------+------------+--------+-----------------------------+
| 001 AAYY  | link AAYY | 2015 | QII     |        110 |      1 | comment 1 for abc 2015 QII  |
| 001 AAYY  | link AAYY | 2015 | QII     |        120 |      0 | comment 2 for abc 2015 QII  |
| 001 AAYY  | link AAYY | 2015 | QII     |        210 |      9 | comment 3 for abc 2015 QII  |
| 001 AAYY  | link AAYY | 2015 | QIII    |        110 |      0 | comment 4 for abc 2015 QIII |
| 002 BBXX  | link BBXX | 2015 | QII     |        110 |      1 | comment 5 for def 2015 QII  |
| 002 BBXX  | link BBXX | 2015 | QII     |        120 |      1 | comment 6 for def 2015 QII  |
| 002 BBXX  | link BBXX | 2015 | QII     |        210 |      1 | comment 7 for def 2015 QII  |
| 002 BBXX  | link BBXX | 2015 | QIII    |        110 |      1 | comment 8 for def 2015 QII  |
+-----------+-----------+------+---------+------------+--------+-----------------------------+

QuestionID and Description are static in row 14, A and B. So as a dynamic output I need everything between C12 to I17.

I tried without luck to do a VLOOKUP by creating a new ID-column by concatenating QuestionID-ArticleID-Year-Quarter.

Can you give me an example how this could be solved?

Thank you very much.

Answer 1

What you are doing is correct, except that, instead of Article ID (which is your comparison value) add link column. Similar concatenation inside VLOOKUP will take care of the rest.

I prepared a sheet for your quick reference. Please follow the secure link. Thanks.

File with updated formula:

Answer 2

Create an ID column that is Question ID & Link. You can then use index and match (or VLookup) to look up any value you need. For example, If your ID column is in column A, in D15 you could put =INDEX($D$2:$D$9,MATCH(A11&"link AAYY",$A$2:$A$9,0))