Find 1st highest and lowest numbers in column closest to subject value

[westend9876]

I have pricing data in Columns A - B that will continue to append over time.
I'm looking for the 1st and 2nd Highest or Lowest values in the column that occurs before the subject value, but closest to the subject value in the column.
I identified the correct answers in Column F-G for the subject values listed in column E and correct answers in Columns J-K for the subject values listed in Column I.
I need a formula that can retrieve the correct values highlighted in yellow in attached example file and noted by "...ANSWER" in the columns below.

COLUMN A High	COLUMN B Low
1.21809​	1.2123​
1.21515​	1.20648​
1.21055​	1.20507​
1.21471​	1.20693​
1.2221​	1.2149​
1.22331​	1.21443​
1.22446​	1.2163​
1.22286​	1.2161​
1.22389​	1.216​
1.22292​	1.21704​
1.22657​	1.22102​
1.22624​	1.21812​
1.22144​	1.21745​
1.22045​	1.21319​
1.22309​	1.21826​
1.22535​	1.2207​

SUBJECT VALUES - in no particular order	1st HIGHEST number in Column A closest to Subject value - ANSWER	2nd HIGHEST number in column A closest to Subject value - ANSWER		SUBJECT VALUES in no particular order	1st LOWEST number in Column B closest to Subject value - ANSWER	2nd LOWEST number in Column B closest to Subject value - ANSWER
1.21055​	1.21515​	1.21809​		1.2207​	1.21826​	1.21319​
1.22045​	1.22144​	1.22624​		1.21443​	1.20693​	1.20507​
1.22144​	1.22624​	1.22657​		1.2163​	1.21443​	1.20693​

 

[GraH - Guido]

Since I'm finding other answers then your manual ones, I'd like to go through the logics here.

I'm looking for the 1st and 2nd Highest or Lowest values in the column that occurs before the subject value.

Looking for value 1.22045 (cell [E3]) in column A, is in row 15. So the results should come form the rows 2-14.

, but closest to the subject value in the column.

Closest means the difference between those found values and the given value in [E3]. Those are the values highlighted in yellow.
However you return the red ones.



So closest means the previous two rows?

If that is the case, you can use
[F2]=INDEX($A$2:$A$17;MATCH($E2;$A$2:$A$17;0)-{1\2}) (might require Control+Shift+Enter).

I do not understand the logic for the MIN values, yet.

 

[westend9876]

Since I'm finding other answers then your manual ones, I'd like to go through the logics here.



Looking for value 1.22045 (cell [E3]) in column A, is in row 15. So the results should come form the rows 2-14.



Closest means the difference between those found values and the given value in [E3]. Those are the values highlighted in yellow.
However you return the red ones.

View attachment 81612

So closest means the previous two rows?

If that is the case, you can use
[F2]=INDEX($A$2:$A$17;MATCH($E2;$A$2:$A$17;0)-{1\2}) (might require Control+Shift+Enter).

I do not understand the logic for the MIN values, yet.

No, closest does not necessarily mean the two previous rows. But, it does refer to proximity to the value in the given row.

 

[GraH - Guido]

Care to explain why the values I highlighted in yellow are not the correct ones?