I currently have an excel worksheet with three columns

```
id annotation person_id
1 yes 1
1 no 2
1 yes 3
```

I'm trying to reformat this on another worksheet into a table that looks like:

```
id 1 2 3
1 yes no yes
```

I'm using this

```
vlookup: =VLOOKUP(A2,sheet2!$F$1:$G$10,2,FALSE)
```

where a2 is the `id`

and f$1:g$10 is the range of data for that particular person.

At the moment I'm doing this per person and its tedious - there's thousands of people. I need a way to incorporate the person_id into my vlookup so that, if the person_id in the column header matches the person_id in sheet 2 and both ids match, then insert the annotation.

# Best How To :

This appears to be a good candidate for an `INDEX-MATCH`

formula, with a multi-condition `MATCH()`

formula to account for both ID criteria. The formula below places the raw data are in `A1:C4`

and the reformatted data in `E1:H2`

, so you will need to change the references:

In cell `F2`

, I've entered `{=INDEX($A$1:$C$4, MATCH(1, ($A$1:$A$4=$E2)*($C$1:$C$4=F$1), 0), 2)}`

, which yields

```
[A] [B] [C] [D] [E] [F] [G] [H]
[1] id annotation person_id ID 1 2 3
[2] 1 yes 1 1 yes no yes
[3] 1 no 2
[4] 1 yes 3
```

The relative references on `$E2`

and `F$1`

allow the filling of the formula across rows and columns.

To explain the formula a bit:

- In the
`INDEX()`

formula, the first argument `$A$1:$C$4`

is the raw data "table" (array).
- The second argument searches for the row where the value
`($A$1:$A$4=$E2)*($C$1:$C$4=F$1)`

is *exactly* (given by the third argument, `0`

) equal to `1`

.
`($A$1:$A$4=$E2)*($C$1:$C$4=F$1)`

is the multiplication of two `TRUE/FALSE`

statements, which is equal to `1`

when the main ID in `col A`

is equal to the ID value in `E2`

, *and* the person ID in `col C`

is equal to the column header in `F1`

.

- The final argument,
`2`

, is the column in the data table from which to look up the result.
- You may want to consider using another
`MATCH()`

formula to dynamically identify the look-up column based on the column header; *e.g.*, instead of `2`

, use `MATCH("annotation", $A$1:$C$1, 0)`

.

Important notes:

- The formula needs to be entered as an
*array formula* using `Ctrl+Shift+Enter`

.
- This method assumes the combinations of
`id`

& `person_id`

are unique.

Also, note that this formula is clearer with named ranges: *e.g.* (using the suggested second `MATCH()`

function instead of `2`

), `{=INDEX(RawData, MATCH(1, (ID=$E2)*(PersonID=F$1), 0), MATCH("annotation", Headers, 0)}`

.