Find Value
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Find Value
Im trying to find the next value in a row after it locates a referenced value.. for example A1=1, A2=2, A3=3, A4=4 (Reference Cell Value = 2) the formula would return 1 and the target is changed to 4 it would return 3.. thank you
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Re: Find Value
I realize im describing a simple If statement but was curious if another method is possible?
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Find Value
Something like this???
C1 is the reference cell and D1 is the result
C1 is the reference cell and D1 is the result
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Re: Find Value
I forgot to mention, numbers can appear repetitive.. for example A1=1, A2=2, A3=2, A4=2, A5=3.. In this example the formula would return 1 as its the last (2) designated value in that range
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Find Value
In my reply I was hinting that the OFFSET function could possible do what you needed (based on the info you supplied at the time).
In order to assist better, would it be possible to upload a desensitized demo workbook to better understand your needs?
TX
In order to assist better, would it be possible to upload a desensitized demo workbook to better understand your needs?
TX
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Re: Find Value
I cant at the moment, but maybe i could provide better clarity.. If my range is A1:A5 and each cell contains the following values, I need a formula that checks the range, references (B1) and provides the value directly above the last reference value (In the case its 1) despite the duplicated value of (2) in A2, A3 and A4
Thanks again
(B1=2) Reference cell
A1=1
A2=2
A3=2
A4=2
A5=3
Thanks again
(B1=2) Reference cell
A1=1
A2=2
A3=2
A4=2
A5=3
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Find Value
Try this...
=OFFSET(INDIRECT("A"&MATCH(B1,A:A,0)),-1,0)
Note: The values would need to be sorted. IOW, you cannot have a duplicate group of numbers repeated further down the list.
=OFFSET(INDIRECT("A"&MATCH(B1,A:A,0)),-1,0)
Note: The values would need to be sorted. IOW, you cannot have a duplicate group of numbers repeated further down the list.
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.