Returning column header of furthest right example of specific text string.

[hc1904]

Hi,
Forum newbie here, this site has helped me many times as I've always found that my query has been asked and answered before but not this time hence my first time post.

So hello! and I hope someone can help on a puzzle that is beyond my Excel knowledge, hopefully I can explain it correctly.

I have the data on sheet1 as below.[TABLE="class: grid, width: 550"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Email [/TD]
[TD]Step A1[/TD]
[TD]Step A2[/TD]
[TD]Step A3[/TD]
[TD]Step B1[/TD]
[TD]Step B2[/TD]
[TD]Step B3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]email1[/TD]
[TD]completed[/TD]
[TD]enrolled[/TD]
[TD]enrolled[/TD]
[TD]enrolled[/TD]
[TD]enrolled[/TD]
[TD]enrolled[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]email2[/TD]
[TD]completed[/TD]
[TD]completed[/TD]
[TD]completed[/TD]
[TD]completed[/TD]
[TD]completed[/TD]
[TD]enrolled[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]email3[/TD]
[TD]completed[/TD]
[TD]completed[/TD]
[TD]completed[/TD]
[TD]enrolled[/TD]
[TD]enrolled[/TD]
[TD]enrolled[/TD]
[/TR]
</tbody>[/TABLE]

On sheet2 I need to first look up the email address and then return the column header of the furthest right example of the text string "completed" like below.
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Email [/TD]
[TD]Last Step Completed[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]email1[/TD]
[TD]Answer would be "Step A1"[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]email2[/TD]
[TD]Answer would be "Step B2"[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]email3[/TD]
[TD]Answer would be "Step A3"[/TD]
[/TR]
</tbody>[/TABLE]

Hopefully that makes sense, I appreciate any help on this.
Thanks

 

[bbotzong]

This formula will get the job done. It's a combination of a SUMPRODUCT() function to determine which column as the word COMPLETED in it. That function is nested inside an INDEX() function to get your result. The sheet names are Sheet1 for your original data, and Sheet2 for the results sheet. You can rename the sheets to make the formulas more readable... Also, it assumes your original table on Sheet1 starts at A1, and the words email1 on Sheet2 starts again on cell A1.

=INDEX(Sheet1!$B$1:$G$1,0,SUMPRODUCT((A1=Sheet1!$A$2:$A$4)*(Sheet1!$B2:$G2="Completed")))

Good luck...

 

[hc1904]

That is wonderful, thanks so much!

I need to learn more about SUMPRODUCT as it's not something I've used much, thanks again.

 

[steve the fish]

Heres a way that doesnt need the same caveats:

=LOOKUP(2,1/(INDEX(Sheet1!$B$2:$G$4,MATCH(A2,Sheet1!$A$2:$A$4,0),0)="completed"),Sheet1!$B$1:$G$1)

 

[hc1904]

Brilliant, thank you - after playing around with the data the original solution would not have worked as my data wouldn't always meet the proposed criteria. Your version however works regardless of which row the email data is on, perfect!

 

[DanteAmor]

Hi @hc1904, welcome to the forum!

The correct formula for sumproduct, could be


=INDEX(Sheet1!$B$1:$G$1,0,SUMPRODUCT(MAX((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$G$4="Completed")*(COLUMN(Sheet1!$B$1:$G$1))))-1)



But I like Steve's formula, it's shorter.

 

[DanteAmor]

I found the detail of the formula of @bbotzong, it should be like this:


=INDEX(Sheet1!$B$1:$G$1,0,SUMPRODUCT((A2=Sheet1!$A$2:$A$4)*(Sheet1!$B$2:$G$4="Completed")))