Index Collect formulae

Gaurav Chauhan

Hi,

Please help me to write the index collect to populate the status on Sheet 1 as marked in Red

Sameer Karkhanis

I am not sure if the INDEX COLLECT will get you what you are looking for as you are looking across the columns and now rows in your Sheet 1.

May be the below helps:

=VLOOKUP([Name]@row,{Name to Training 2 Range in Sheet 1}, IF([Training Name]@row = "Training 1", 2, 3), True) 

I would recommend that you add the value "Not Assigned" in Sheet 1 itself where you have blank cells today.

Not recommended but since you also want to set the Status value to "Not Assigned" if the cell in Sheet 1 is BLANK you will need to use,

=IF(ISBLANK(=VLOOKUP([Name]@row,{Name to Training 2 Range in Sheet 1}, IF([Training Name]@row = "Training 1", 2, 3), True)), "Not Assigned", =VLOOKUP([Name]@row,{Name to Training 2 Range in Sheet 1}, IF([Training Name]@row = "Training 1", 2, 3), True))

Gaurav Chauhan

Thanks for your response. I am trying to collect the status of the trainings tied to the users on Sheet 1 marked in Red

Have re written the sheet for ref. Also, where are you collecting details of Status in the formulae

@sandeep kumar

Kelly Moore

Hey @Gaurav Chauhan

Try this.

Training 1

=INDEX(COLLECT({Sheet 2 Status}, {Sheet 2 User email ID v2}, [User email ID}@row, {Sheet 2 Training Name}, "Training 1"),1)

Training 2

=INDEX(COLLECT({Sheet 2 Status}, {Sheet 2 User email ID v2}, [User email ID}@row, {Sheet 2 Training Name}, "Training 2"),1)

*Don't forget that you cannot simply copy paste the formulas above into your sheet. The cross sheet references must be individually inserted. Your range names will be different than mine unless you edit the name of the generically created range names prior to inserting in your sheet.

Do the formulas work for you?

Kelly