Function that writes name depending on two conditions

Question

I would like to know how to implement this in an excel formula: I have a column with some names, a row with different numbers (dates), and the remaining values work as "keys". For instance:

           3    10  17  24
person1   HE            
person2         GB  HE  
person3         TY      OK

In another page I have this:

       3    10
HE     ??   ??
GB     ??   ??
TY     ??   ??

And I want to get the following:

           3            10
HE      person1         -
GB         -         person2
TY         -         person3

Thus I want to see who (which person in the first column of the first table) has each "key" (HE, GB, TY...) in each day, and write his/her name depending on the date number (3, 10...) in the last table. And there's no match, leave it empty or with a "-" in it.

Thank you!

score 0 · Answer 1 · answered Aug 29 '20 at 20:34

0

You can try and adapt the formula below:

=IFNA(INDEX($A$2:$A$4,MATCH($A$8:$A$10,B2:B4,0)),"-")

Drag it to fill the other cells

answered Aug 29 '20 at 20:34

Reddy Lutonadio

20,570

score 0 · Answer 2 · answered Aug 31 '20 at 08:51

Try to use Power Query to get the result:

Select Range- go to Data- From Range\Table:
Select Column 3, 10, 17 and 24- go to Transform- select Unpivot Columns:
Select Attribute column- Select Pivot Column- Value Column= Column1 and under Advanced options, Aggregate Value Function= Don't Aggregate- Close & Load:

Function that writes name depending on two conditions

2 Answers2