FIND VALUE AND POSITION FROM THE NEAREST 2 Columns FROM TOP TO LOW

[Excelpromax123]

Hello everyone !
I need a formula to calculate the value and the ordinal position according to the 2 closest columns from top to bottom .
Sincerely thank

If found column A and column B with input value, get same as Vlookup and Match

If Column A and Column B are not in a row, get the closest row from top to bottom

The rest of the cases give the result 0 .

Sincerely thank
lINK

EXCEL FIND.xlsx

Sheet1 DATA,INPUT,OUTPUT No,Name,Number,No,Name,Number,MATCH 10,C,50,8,C,91,6 9,B,70 8,P,90 7,A,50 6,B,70 5,C,91 4,A,50 3,B,75 2,C,90 1,C,82

docs.google.com

 

[Sahak]

Hello
Try this:
Formula in H3

=IFERROR(OFFSET(C3,MATCH(F3,(OFFSET(INDIRECT("B"&MATCH(E3,A3:A12,0)+2),0,0):B12),0)+MATCH(E3,A3:A12,0)-2,0),0)

Formula in I3

=IF(H3=0,0,MATCH(H3,C3:C12,0))