Find a value, return the value of another cell
I need a formula for the following:
Given range BA2:BL2, I need to find the first value in this range 0, reading left to right, and then return the text in the corresponding cell in the range BA1:BL1. Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the text in BD1 returned. Clear? I hope so. Thanks. Dave -- Brevity is the soul of wit. |
Find a value, return the value of another cell
Give this a whirl. You can use it in code or as a UDF...
Sub whatever() MsgBox FindStuff(Sheets("sheet1").Range("BA2:BL2")) End Sub Public Function FindStuff(ByVal rngToSearch As Range) As String Dim rng As Range FindStuff = "No Value Found" For Each rng In rngToSearch If rng.Value < 0 Then FindStuff = rng.Offset(-1, 0).Value Exit For End If Next rng End Function -- HTH... Jim Thomlinson "Dave F" wrote: I need a formula for the following: Given range BA2:BL2, I need to find the first value in this range 0, reading left to right, and then return the text in the corresponding cell in the range BA1:BL1. Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the text in BD1 returned. Clear? I hope so. Thanks. Dave -- Brevity is the soul of wit. |
Find a value, return the value of another cell
Interesting.
Let's say I also want to do a similar search for range BA3:BL3 but still return the value in range BA1:BL1. Would I change the rng.Offset(-1, 0).Value to rng.Offset(-2, 0).Value ? Dave -- Brevity is the soul of wit. "Jim Thomlinson" wrote: Give this a whirl. You can use it in code or as a UDF... Sub whatever() MsgBox FindStuff(Sheets("sheet1").Range("BA2:BL2")) End Sub Public Function FindStuff(ByVal rngToSearch As Range) As String Dim rng As Range FindStuff = "No Value Found" For Each rng In rngToSearch If rng.Value < 0 Then FindStuff = rng.Offset(-1, 0).Value Exit For End If Next rng End Function -- HTH... Jim Thomlinson "Dave F" wrote: I need a formula for the following: Given range BA2:BL2, I need to find the first value in this range 0, reading left to right, and then return the text in the corresponding cell in the range BA1:BL1. Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the text in BD1 returned. Clear? I hope so. Thanks. Dave -- Brevity is the soul of wit. |
Find a value, return the value of another cell
That would do it... If you need it to be dynamic then use something like
this... Public Function FindStuff(ByVal rngToSearch As Range) As String Dim rng As Range FindStuff = "No Value Found" For Each rng In rngToSearch If rng.Value < 0 Then FindStuff = Cells(1, rng.Column).Value Exit For End If Next rng End Function -- HTH... Jim Thomlinson "Dave F" wrote: Interesting. Let's say I also want to do a similar search for range BA3:BL3 but still return the value in range BA1:BL1. Would I change the rng.Offset(-1, 0).Value to rng.Offset(-2, 0).Value ? Dave -- Brevity is the soul of wit. "Jim Thomlinson" wrote: Give this a whirl. You can use it in code or as a UDF... Sub whatever() MsgBox FindStuff(Sheets("sheet1").Range("BA2:BL2")) End Sub Public Function FindStuff(ByVal rngToSearch As Range) As String Dim rng As Range FindStuff = "No Value Found" For Each rng In rngToSearch If rng.Value < 0 Then FindStuff = rng.Offset(-1, 0).Value Exit For End If Next rng End Function -- HTH... Jim Thomlinson "Dave F" wrote: I need a formula for the following: Given range BA2:BL2, I need to find the first value in this range 0, reading left to right, and then return the text in the corresponding cell in the range BA1:BL1. Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the text in BD1 returned. Clear? I hope so. Thanks. Dave -- Brevity is the soul of wit. |
Find a value, return the value of another cell
You could also use a formula:
=INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) or just in case: =IF(COUNTIF($BA2:$BL2,""&0)=0,"No positive numbers", INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0))) (still an array formula) Dave F wrote: I need a formula for the following: Given range BA2:BL2, I need to find the first value in this range 0, reading left to right, and then return the text in the corresponding cell in the range BA1:BL1. Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the text in BD1 returned. Clear? I hope so. Thanks. Dave -- Brevity is the soul of wit. -- Dave Peterson |
Find a value, return the value of another cell
Yeah I was thinking it should be something with INDEX and MATCH. I have to
figure those two functions out. I like how you trap errors in the second formula. Thanks. Dave -- Brevity is the soul of wit. "Dave Peterson" wrote: You could also use a formula: =INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) or just in case: =IF(COUNTIF($BA2:$BL2,""&0)=0,"No positive numbers", INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0))) (still an array formula) Dave F wrote: I need a formula for the following: Given range BA2:BL2, I need to find the first value in this range 0, reading left to right, and then return the text in the corresponding cell in the range BA1:BL1. Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the text in BD1 returned. Clear? I hope so. Thanks. Dave -- Brevity is the soul of wit. -- Dave Peterson |
Find a value, return the value of another cell
Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions03.html (for =index(match())) Dave F wrote: Yeah I was thinking it should be something with INDEX and MATCH. I have to figure those two functions out. I like how you trap errors in the second formula. Thanks. Dave -- Brevity is the soul of wit. "Dave Peterson" wrote: You could also use a formula: =INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) or just in case: =IF(COUNTIF($BA2:$BL2,""&0)=0,"No positive numbers", INDEX($BA$1:$BL$1,MATCH(TRUE,$BA2:$BL20,0))) (still an array formula) Dave F wrote: I need a formula for the following: Given range BA2:BL2, I need to find the first value in this range 0, reading left to right, and then return the text in the corresponding cell in the range BA1:BL1. Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the text in BD1 returned. Clear? I hope so. Thanks. Dave -- Brevity is the soul of wit. -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 01:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com