Finding the ROW value in an Excel WB using Powershell based on the MAX value

I have an Excel workbook that has values from F13 to F40, these values change every Powershell Job run we do.

I need to find the Top 3 values in the range of F13 - F40 and I need to get the Row Number of each of the three values

So far I can get the 3 values with this Code in Powershell

$EXCEL = New-Object -ComObject Excel.Application
$EXCEL.visible = $false
$basefinalwb = $EXCEL.workbooks.open("C:\relax\Book1.xlsx")
$basefinalws = $basefinalwb.worksheets.Item("Current Results")
$MAX1 = $basefinalws.cells.item(1,1).Formula = "=LARGE(F13:F40, 1)"
$MAX2 = $basefinalws.cells.item(2,1).Formula = "=LARGE(F13:F40, 2)"
$MAX3 = $basefinalws.cells.item(3,1).Formula = "=LARGE(F13:F40, 3)"
$MAX11 = $basefinalws.cells.item(1,1).value2
$MAX21 = $basefinalws.cells.item(2,1).value2
$MAX31 = $basefinalws.cells.item(3,1).value2
Write-host Max11 is $max11
Write-host Max21 is $max21
Write-host Max31 is $max31


The Results are this:
Max11 is 0.112570356472795
Max21 is 0.097560975609756
Max31 is 0.0881801125703564
Now I need to figure out which Row each of those Values came from
 
So I have tried this code and it returns blank

$Cell2 = $basefinalws.range("F1","F40").find($MAX21)
$Cell2.Value2
$Cell2.Row
$Cell2

In my testing I even pulled the Value on the Cell the number came from and they match … I just cannot figure out why “find” isn’t working.
 
Now the only thing I can think of is that all the values in the F column are from Formulas =(D13-C13)/C13 but the above code does grab the value of the cell just fine it just can’t re-find it so I can get the Row Number
 
If anyone knows where I am going wrong, help is appreciated or maybe an easier way to accomplish the task?
 
Thank you!
 

You could use the ImportExcel module for this.

The code would look something like this:

#Import the Excel Workbook
$sheet = Import-Excel E:\temp\values.xlsx -NoHeader

#Grab cells 13 - 40, sort and select the last 3 (largest numbers)
$top3 = $sheet.p6[12..39] | Sort | Select -Last 3

#Find the index of the top 3 values, add 1 to get the cell
$top3 | foreach {Write-Output "Cell is F$($sheet.p6.IndexOf($_)+1)"}

If your sheet has headers you can omit the -Noheader switch. The range will be 11 to 38 and you’ll need to add 2 to the index.