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

#### hc1904

##### New Member
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.
 A B C D E F G 1 Email Step A1 Step A2 Step A3 Step B1 Step B2 Step B3 2 email1 completed enrolled enrolled enrolled enrolled enrolled 3 email2 completed completed completed completed completed enrolled 4 email3 completed completed completed enrolled enrolled enrolled

<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.
 A B 1 Email Last Step Completed 2 email1 Answer would be "Step A1" 3 email2 Answer would be "Step B2" 4 email3 Answer would be "Step A3"

<tbody>
</tbody>

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

### 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
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
That is wonderful, thanks so much!

#### steve the fish

##### Well-known Member
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
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
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
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: