Read Excel data with Powershell and write to a variable

User input can be read like this:

$num = Read-Host "Store number"

Excel can be handled like this:

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\path\to\your.xlsx")
$ws = $wb.Sheets.Item(1)

Looking up a value in one column and assigning the corresponding value from another column to a variable could be done like this:

for ($i = 1; $i -le 3; $i++) {
  if ( $ws.Cells.Item($i, 1).Value -eq $num ) {
    $GoLiveDate = $ws.Cells.Item($i, 2).Value
    break
  }
}

Don't forget to clean up after you're done:

$wb.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

I find it preferable to use an OleDB connection to interact with Excel. It's faster than COM interop and less error prone than import-csv. You can prepare a collection of psobjects (one psobject is one row, each property corresponding to a column) to match your desired target grid and insert it into the Excel file. Similarly, you can insert a DataTable instead of a PSObject collection, but unless you start by retrieving data from some data source, PSObject collection way is usually easier.

Here's a function i use for writing a psobject collection to Excel:

function insert-OLEDBData ($file,$sheet,$ocol) {

    {
        "xlsb$" 
            {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$File`";Extended Properties=`"Excel 12.0;HDR=YES;IMEX=1`";"}
        "xlsx$"
            {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$File`";Extended Properties=`"Excel 12.0 Xml;HDR=YES;IMEX=1`";"}
    }
    $OLEDBCon = New-Object System.Data.OleDb.OleDbConnection($cs)

    $hdr = $oCol|gm -MemberType NoteProperty|%{$_.name}

    $names = '[' + ($hdr-join"],[") + ']'
    $vals = (@("?")*([array]$hdr).length)-join','

    $sql = "insert into [$sheet`$] ($names) values ($vals)"

    $sqlCmd = New-Object system.Data.OleDb.OleDbCommand($sql)
    $sqlCmd.connection = $oledbcon

    $cpary = @($null)*([array]$hdr).length

    $i=0
    [array]$hdr|%{([array]$cpary)[$i] = $sqlCmd.parameters.add($_,"VarChar",255);$i++}
    $oledbcon.open()

    for ($i=0;$i-lt([array]$ocol).length;$i++)
    {
        for ($k=0;$k-lt([array]$hdr).length;$k++)
        {
            ([array]$cpary)[$k].value = ([array]$oCol)[$i].(([array]$hdr)[$k])
        }
        $res = $sqlCmd.ExecuteNonQuery()
    }
    $OLEDBCon.close()

}