Vlookup and & Functions

I do not catch the B column with different names assigned to other. But as for certificates - Use array formula (ctrl+Shift+Enter committed)

=IFERROR(INDEX(Sheet1!$E$2:$E$7,MATCH($A2&C$1,Sheet1!$A$2:$A$7&Sheet1!$D$2:$D$7,0)),"")

see attachment
(I changed format in row 1 to reflect dates in column D)

Hi,

a first answer

The formula try to match Names of column A and month


In C2 and down and to the right

=IFERROR(INDEX(Sheet1!$E$2:$E$7,MATCH($A2&COLUMN(A$1),INDEX(Sheet1!$A$2:$A$7&MONTH(Sheet1!$D$2:$D$7),),0)),"")

Hope it's a little step forward


Edit: sorry Kaper, same formula (more or less)

Hi,

in the file attached every cell can house up to three different certifications.

=IFERROR(INDEX(Sheet1!$E$2:$E$7,SMALL(IF(Sheet1!$A$2:$A$7=$A2,IF(MEIF(Sheet1!$D$2:$D$7)=COLUMN(A$1),ROW($A$2:$A$7)-1)),1)),"")&" "&IFERROR(INDEX(Sheet1!$E$2:$E$7,SMALL(IF(Sheet1!A2:A7=$A2,IF(MEIF(Sheet1!$D$2:$D$7)=COLUMN(A$1),ROW($A$2:$A$7)-1)),2)),"")&IFERROR(INDEX(Sheet1!$E$2:$E$7,SMALL(IF(Sheet1!$A$2:$A$7=$A2,IF(MEIF(Sheet1!$D$2:$D$7)=COLUMN(A$1),ROW($A$2:$A$7)-1)),3)),"")

Formulas are array INDEX to be confirmed with control+shift+enter.

I'm sure there are more convenient way to get the certifications

Hi,

just add a segment to the formula changing the last parameter of INDEX


&IFERROR(INDEX(Sheet1!$E$2:$E$7,SMALL(IF(Sheet1!A2:A7=$A2,IF(MEIF(Sheet1!$D$2:$D$7)=COLUMN(A$1),ROW($A$2:$A$7)-1)),4)),"")&ERROR(INDEX(Sheet1!$E$2:$E$7,SMALL(IF(Sheet1!$A$2:$A$7=$A2,IF(MEIF(Sheet1!$D$2:$D$7)=COLUMN(A$1),ROW($A$2:$A$7)-1)),5)),"")


A pivot table could handle this job more ecologically: array formula do not always get along with the processor.

Hi again,

thanks for your kind feedback. If you are free to write in column A three/four/five times each name formulas could easier to adjust. But I do not want modify the layout of your data.

Regards

Hi,

please see the file attached. Cells in D2:D5 should house 1 (see the formats).

Just a little correction. More important is how to confirm formulas in sheet2

Hope it's a little step forward.

Regards