# Help with combining Lookup, Index and Left function

#### MoonHopper99

##### New Member
Hello

I am attempting to combine these functions. I have successfully combined them 2 at time but am struggling to find the correct way to put them all in one formula.

I am trying to achieve the following
1. Look up the last occurance of the "to In Progress" in a given row (there are times where an item is in progress multiple times)
2. Match and grab the corresponding timestamp (the cell below) Ex. If the return value is F16, then I need to grab the timestamp for F17)
3. I only need the date from the referenced cell for the purpose of this particular formula (hence the left)
4. I need to be able to drag down the formal in consecutive cells in a column (see the 2nd uploaded image) (hence the index)

Here are the formulas I have been working with and trying to combine

Grabs the Date and Skips 2 rows so I can drag formula down
=INDEX(LEFT('Full Sprint Data (2)'!\$E\$11:\$E\$113,10),1+(2*(ROWS(F\$15:F15)-1)))

Looks up the last occurence of "to In Progress" in the given row and matches it with the corresponding timestamp
=IFERROR(LEFT(LOOKUP(2,1/('Full Sprint Data (2)'!\$E14:\$R14='Full Sprint Data (2)'!\$A\$4),'Full Sprint Data (2)'!\$E15:\$R15),10),"")

I need to index so that when I drag down
!\$E14:\$R14 and \$E15:\$R15 becomes !\$E16:\$R16 and \$E17:\$R17
instead of what it is currently doing (not shown in the screenshots)
!\$E14:\$R14 and \$E15:\$R15 becomes !\$E15:\$R15 and \$E16:\$R16

I really hope I provided enough context.

Thanks!

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

bump

Replies
10
Views
214
Replies
9
Views
126
Replies
9
Views
190
Replies
1
Views
114
Replies
2
Views
77