Create an array of values in excel

Question

I have a table with not unique names like below:

|-|---------------------|------------------|
| |          A          |         B        |
|-|---------------------|------------------|
|1|         John        |    01.01.1900    |
|-|---------------------|------------------|
|2|         Adam        |    01.02.1900    |
|-|---------------------|------------------|
|3|         John        |    01.03.1900    |
|-|---------------------|------------------|
|4|         Jane        |    01.04.1900    |
|-|---------------------|------------------|

And I have another table with unique names like:

|-|---------------------|----------|----------|----------|----------|
| |          A          |     B    |     C    |     D    |     E    |
|-|---------------------|----------|----------|----------|----------|
|1|         Name        |01.01.1900|01.02.1900|01.03.1900|01.04.1900|
|-|---------------------|----------|----------|----------|----------|
|2|         John        |          |          |          |          |
|-|---------------------|----------|----------|----------|----------|
|3|         Adam        |          |          |          |          |
|-|---------------------|----------|----------|----------|----------|
|4|         Jane        |          |          |          |          |
|-|---------------------|----------|----------|----------|----------|

Now I want to highlight the cells in the second table if name and date match with the first table and make it look like:

|-|---------------------|----------|----------|----------|----------|
| |          A          |     B    |     C    |     D    |     E    |
|-|---------------------|----------|----------|----------|----------|
|1|         Name        |01.01.1900|01.02.1900|01.03.1900|01.04.1900|
|-|---------------------|----------|----------|----------|----------|
|2|         John        |    1     |          |    1     |          |
|-|---------------------|----------|----------|----------|----------|
|3|         Adam        |          |    1     |          |          |
|-|---------------------|----------|----------|----------|----------|
|4|         Jane        |          |          |          |    1     |
|-|---------------------|----------|----------|----------|----------|

I've tried using the FILTER() function in B2 cell (for John only): =IF(B$1=FILTER('dates'!$B$2:$B$4;'dates'!$A$2:$A$4=$A2);1;0) where "dates" is name of the sheet with the first table and then expanding it to other cells in the 2nd row, but when I do it I get the following:

|-|---------------------|----------|----------|----------|----------|
| |          A          |     B    |     C    |     D    |     E    |
|-|---------------------|----------|----------|----------|----------|
|1|         Name        |01.01.1900|01.02.1900|01.03.1900|01.04.1900|
|-|---------------------|----------|----------|----------|----------|
|2|         John        |    1     |    0     |    0     |    0     |
|-|---------------------|----------|----------|----------|----------|
|3|         Adam        |    0     |    0     |    1     |    0     |
|-|---------------------|----------|----------|----------|----------|
|4|         Jane        |          |          |          |          |
|-|---------------------|----------|----------|----------|----------|

Do you have any ideas?

I did not use it. So I should build an array with XLOOKUP and then use FILTER? — fustacre
– fustacre, Commented Nov 25, 2020 at 7:28
You have to change your question, visualize what you are doing including your wanted result. That will make things easier to understand. See How to Ask a question with an minimal reproducible example for some general guidelines. — JvdV
– JvdV, Commented Nov 25, 2020 at 8:19
@JvdV thank you. I've updated the question, hope now it is more clear. — fustacre
– fustacre, Commented Nov 25, 2020 at 9:15

JvdV · Accepted Answer · 2020-11-25 09:25:46Z

1

Right, your edit makes your question a lot more clearer, you could try:

Formula in E2:

=COUNTIFS($A:$A,$D2,$B:$B,E$1)

Drag down and right.

Also, since you happen to have Excel O365, you can do the whole matrix in a single go with the following formula in E2:

=COUNTIFS(A1:A4,D2:D4,B1:B4,E1:H1)

Now the rest of the matrix will auto-fill.

edited Nov 25, 2020 at 9:25

answered Nov 25, 2020 at 9:19

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create an array of values in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related