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

hc1904

New Member
Joined
Nov 4, 2019
Messages
3
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.
ABCDEFG
1Email Step A1Step A2Step A3Step B1Step B2Step B3
2email1completedenrolledenrolledenrolledenrolledenrolled
3email2completedcompletedcompletedcompletedcompletedenrolled
4email3completedcompletedcompletedenrolledenrolledenrolled

<tbody>
</tbody>

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.
AB
1Email Last Step Completed
2email1Answer would be "Step A1"
3email2Answer would be "Step B2"
4email3Answer would be "Step A3"

<tbody>
</tbody>

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

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

bbotzong

New Member
Joined
Dec 17, 2003
Messages
22
Office Version
365, 2019, 2016, 2013, 2010
Platform
Windows
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

New Member
Joined
Nov 4, 2019
Messages
3
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,942
Office Version
365
Platform
Windows
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

New Member
Joined
Nov 4, 2019
Messages
3
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

Well-known Member
Joined
Dec 3, 2018
Messages
10,271
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
10,271
Office Version
2007
Platform
Windows
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")))
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,408
Messages
5,414,228
Members
403,520
Latest member
Pineappleman

This Week's Hot Topics

Top