Create table out of existing table matching a condition

ironzionlion

New Member
Joined
May 3, 2016
Messages
1
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:


Code:
 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:


Code:
 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,
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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:
Code:
=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
 
Upvote 0

Forum statistics

Threads
1,226,695
Messages
6,192,475
Members
453,726
Latest member
JoeH57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top