Create table out of existing table matching a condition

[ironzionlion]

I want to create a table in Excel that extracts the part of a table that matches a certain condition. If possible without using macros. Here is a plot:

 A      B      C
    0      1
    6      2
    12     3
    18     4     10
    0      5
    6      6
    12     7
    18     8     26
    0      9
    6     10
    12    11
    18    12     42

When looking for 26, this is what I want to obtain:

 D
    5
    6
    7
    8

I get the value D4 = LOOKUP(26;C:C;B:B) (which at the end results in D4 = B8) and then I am trying to get D3 somehow...


I have tried using LOOKUP, and also trying to get the reference from a cell, in this case D4 and then modify the path out of it, like "copy the value in B (8-1)", but I don't know how to do it.


I am also sure that there is a way easier way to do it, but it has been a while since I started thinking about...


Any help will be appreciated


Best,

 

[XLearner]

Hi,

Assuming data are in A1:C20 and the value to match (ie 26) is in E1, where you want to report the 1st number try:

=IFERROR(INDEX(OFFSET($B$1, MAX((IF($C$1:$C$20="",FALSE,ROW($C$1:$C$20)) < MATCH($E$1,$C$1:$C$20,0))*ROW($C$1:$C$20)),0,
   MATCH($E$1,$C$1:$C$20,0)-MAX((IF($C$1:$C$20="",FALSE,ROW($C$1:$C$20)) < MATCH($E$1,$C$1:$C$20,0))*ROW($C$1:$C$20))),
   ROW(1:1)),"")

Validate with [Ctrl]+[Shift]+[Enter] (array formula) and copy down as necessary

Regards
XLearner