Return range from a Data Table based on Lookup Criteria

[BenefitRich]

I am trying to come up with a formula that will let me return an array of X cells beginning with the the 1st NON-ZERO value from a data table:

ItemA:0|1|2|8

ItemB:9|0|3|6

ItemC:4|2|0|5


So, given the Item and say 3 cells, the formula Would return something like this:

[pre]

ItemA:1|2|8
ItemB:9|0|3
ItemC:4|2|0[/pre]
I know how to do this if I know the row involved, but I am extracting from a large table where I won't know the right row to look across. I'm sure there is a combination of INDEX MATCH and OFFSET that will do this, but I don't know how :(


=INDEX(Table,MATCH(Item_Being_Looked_Up,List_Of_Items,0),???___MATCH(TRUE,______????>0???)

Hopefully this makes enough sense for my question. I'm sorry, I've been at this for hours and am not making any progress.


If I know the row involved I can use something like OFFSET(FirstCell,,MATCH(TRUE,FirstCell:LastCell>0,0)-1,1,1) but that doesn't seem to work when the FirstCell and LastCell are effectively in different rows.

 

[NARAYANK991]

Hi ,


Try this array formula , entered using CTRL SHIFT ENTER :


=OFFSET(INDEX($B$1:$E$3,MATCH(A10,$A$1:$A$3,0),0),,MATCH(TRUE,INDEX($B$1:$E$3,MATCH(A10,$A$1:$A$3,0),0)<>0,0)-1,1,$H$1)


H1 contains the number of elements you want in the output , in this case 3.


The array formula is supposed to be entered after selecting 3 cells , say B10 , C10 , D10 , and then entering the formula by using CTRL SHIFT ENTER. If you select more cells , say 4 , then the additional cell will display #N/A.


A10 contains the data from column A of your data range ; in this case A10 will have ItemA.


Narayan

 

[BenefitRich]

Thank you NARAYANK991, I will have to try that. It is almost as convoluted as my own answer which I came up with by using an embedded INDEX inside of an INDEX to both provide a range and to test against! This is not pretty, but it does work:


'=INDEX(INDEX(InnerTable,MATCH(A1,ItemList,0),0),,MATCH(TRUE,INDEX(InnerTable,MATCH(A1,ItemList,0),0)>0,0)+HowManyColumnsOver)'