# Return another value from same row

**URL:** <https://forums.powershell.org/t/return-another-value-from-same-row/10357>\
**Category:** PowerShell Help\
**Created:** [March 21, 2018, 9:23pm UTC](https://forums.powershell.org/t/return-another-value-from-same-row/10357 "2018-03-21T21:23:35Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![gazza](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/gazza/32/130_2.png) [@gazza](https://forums.powershell.org/u/gazza)\
**Post date:** [March 21, 2018, 9:23pm UTC](https://forums.powershell.org/t/return-another-value-from-same-row/10357/1 "2018-03-21T21:23:35Z")

</div>

Hey Guys. Can someone help me?  
I am wanting to return a value from the same row but am unsure how it’s done. I can get the row number but then I am stuck.

Can someone point me in the right direction of what the next step would be.

```
$File = "C:\Temp\DemoData.xlsx"

$hostname = 'hostnameHere'

#Serial in 'V1'
#End date in 'H1'

# Setup Excel, open $File and select the 2nd worksheet
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Workbook = $Excel.workbooks.open($file)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(2)

$Range = $Worksheet.Range("V1").EntireColumn
$Cell = $Range.find($hostname)

$RowNumber = $Cell.row

$RowNumber
```

---

<div class="post-metadata">

**Author:** ![chris-bakker](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/chris-bakker/32/6356_2.png) [@chris-bakker](https://forums.powershell.org/u/chris-bakker)\
**Post date:** [March 22, 2018, 3:33am UTC](https://forums.powershell.org/t/return-another-value-from-same-row/10357/2 "2018-03-22T03:33:49Z")

</div>

Something like this?

```
$worksheet=$objExcel.Worksheets.Item("Sheet1")
$Range = $Worksheet.Range("V1").EntireColumn.find($hostname)
$WorkSheet.Cells.Item($range.row,8).value2
```

---

<div class="post-metadata">

**Author:** ![aapeli-hietikko](https://avatars.discourse-cdn.com/v4/letter/a/47e85d/32.png) [@aapeli-hietikko](https://forums.powershell.org/u/aapeli-hietikko)\
**Post date:** [March 22, 2018, 4:47am UTC](https://forums.powershell.org/t/return-another-value-from-same-row/10357/3 "2018-03-22T04:47:27Z")

</div>

```
$File = "C:\Temp\DemoData.xlsx"

$hostname = 'hostnameHere'

#Serial in 'V1'
#End date in 'H1'

# Setup Excel, open $File and select the 2nd worksheet
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Workbook = $Excel.workbooks.open($file)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(2)

$Range = $Worksheet.Range("V1").EntireColumn
$Cell = $Range.find($hostname)
$RowNumber = $Cell.row

#H = 8
$Worksheet.Cells.Item($RowNumber, 8).text
```

---

<div class="post-metadata">

**Author:** ![gazza](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/gazza/32/130_2.png) [@gazza](https://forums.powershell.org/u/gazza)\
**Post date:** [March 22, 2018, 4:58pm UTC](https://forums.powershell.org/t/return-another-value-from-same-row/10357/4 "2018-03-22T16:58:16Z")

</div>

Thanks guys, my problem was that I wasn’t giving it a number but the column letter like I was when searching the range.

I’m still getting an error when it runs but it still works…

```
Exception from HRESULT: 0x800A03EC
At C:\Users\USername\OneDrive\Scripts\Lookup-LeaseShedule.ps1:22 char:2
+ $EndDate = $Worksheet.Cells.Item($CellData.row, 8).text
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
```

```
cls
$File = "C:\Temp\DemoData.xlsx"

$OU = OUpath

#Get computer name and info field for each computer in $OU
$Computers = Get-ADComputer -Filter * -SearchBase $OU -Properties Name,Description -Server domain | Select-Object Name,Description

# Setup Excel, open $File and select the 2nd worksheet
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Workbook = $Excel.workbooks.open($file)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(2)

$Range = $Worksheet.Range("V1").EntireColumn

Foreach ($Computer in $Computers) {
	$CellData = $Range.find($Computer.name)
	$EndDate = $Worksheet.Cells.Item($CellData.row, 8).text
	$Description = $Computer.Description
	Get-ADComputer $Computer.name -Properties Name,Description | Set-ADComputer -Description "Lease Expiry: $EndDate - $Description"
}
```

---

<div class="post-metadata">

**Author:** ![chris-bakker](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/chris-bakker/32/6356_2.png) [@chris-bakker](https://forums.powershell.org/u/chris-bakker)\
**Post date:** [March 23, 2018, 2:35am UTC](https://forums.powershell.org/t/return-another-value-from-same-row/10357/5 "2018-03-23T02:35:47Z")

</div>

```
EndDate = $Worksheet.Cells.Item($CellData.row, 8).text
```

You need to check if $CellData is filled.  
If the row is not found. then this line will fail

---

<div class="post-metadata">

**Author:** ![gazza](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/gazza/32/130_2.png) [@gazza](https://forums.powershell.org/u/gazza)\
**Post date:** [March 25, 2018, 2:52pm UTC](https://forums.powershell.org/t/return-another-value-from-same-row/10357/6 "2018-03-25T14:52:11Z")

</div>

Not sure how I missed that one…  
thanks.

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:36pm UTC](https://forums.powershell.org/t/return-another-value-from-same-row/10357/7 "2024-05-16T20:36:05Z")

</div>