Help with combining Lookup, Index and Left function

MoonHopper99

New Member
Joined
Jul 31, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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!

Excel1.jpg


Excel2.jpg
 

Some videos you may like

Excel Facts

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

Watch MrExcel Video

Forum statistics

Threads
1,113,755
Messages
5,544,000
Members
410,589
Latest member
MrBK
Top