Find Value

[JoeExcelHelp]

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

[JoeExcelHelp]

I realize im describing a simple If statement but was curious if another method is possible?

[Rudi]

Something like this???
C1 is the reference cell and D1 is the result

1.jpg

[JoeExcelHelp]

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

[Rudi]

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

[JoeExcelHelp]

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

[Rudi]

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.

1.jpg

[JoeExcelHelp]

Thanks