Adjust Lookup formula to find first entry

mljohn · Jun 10th 2021

I have a lookup formula that finds the last entry and returns the date in that column in the header.

IFERROR(LOOKUP(2,1/(B2:IM2<>""),$B$1:$IM$1),"")

How do I adjust it to find the first entry and give me the first date in that column in the header?

**royUK** · Jun 10th 2021

Why do you need to look up the date? The MIN Function will return the earliest date.

**royUK** · Jun 10th 2021

Will the workbooks be open. This is some code that combines all open workbooks

Code

Sub CombineAllOpenWorkbooks()

Dim NewFileName As String
Dim c As Integer
Dim SheetCount As Integer

    NewFileName = ActiveWorkbook.Name
    c = 1
    Do Until c = 0
        If Windows(c).Visible = True Then
            Windows(c).Activate
            MsgBox ("New file to be created")
            NewFileName = Application.GetSaveAsFilename _
                (, "Microsoft Excel Workbook (*.xl*),*.xl*")
            ActiveWorkbook.SaveAs FileName:=NewFileName, _
                FileFormat:=xlWorkbookNormal
            NewFileName = ActiveWorkbook.Name
            ActiveSheet.Select
            c = 0
            SheetCount = ActiveWorkbook.Sheets.Count
        Else
            c = c + 1
        End If
    Loop
    For c = 1 To Workbooks.Count
        If Windows(c).Parent.Name <> NewFileName And Windows(c).Visible = True Then
            Windows(c).Activate
            ActiveWorkbook.Sheets.Copy after:=Workbooks(NewFileName).Sheets(SheetCount)
        End If
    Next c
    
End Sub

Display More

bosco_yip · Jun 10th 2021

Try to use Index+Match function to return the first entry date, as in :

=IFERROR(INDEX($B$1:$IM$1,MATCH(TRUE,INDEX(B2:IM2<>"",0),0)),"")

Regards

Adjust Lookup formula to find first entry

Participate now!

Share