Forumla to pull from a lookup table row for row excluding zeros

[Rtlane]

I have a lookup table similar to below:

id	object	channel
1	2	8665
1	3	8364
1	4	0
2	1	0
2	5	8763
2	8	8310
3	10	41367
3	12	45171

I have another table similar to the below that has another id column (id 2). This is actually created from another table elsewhere in my workbook. I want the table belows object 2’s and channel 2’s column to be taken from the above table excluding the rows with 0 for the channels. It would look like this....

id 2	object 2	channel 2
1	2	8665
1	3	8364
2	5	8763
2	8	8310

Can you help me with a formula to use in the channel 2 column of my second table to fill in from the channel column in the first table excluding zeros. Im assuming this would incorporate a vlookup.

[Rtlane]

What I meant to say was that i needed a formula to use in the object 2 and channel 2 columns.

[FlameRetired]

Hi Rtlane. Welcome to the forum.

Please try this.

	A	B	C	D	E	F	G	H
1	id	object	channel		id 2	object 2	channel 2
2	1	2	8665		1	2	8665	In E2:G7 - =IFERROR(INDEX(A$2:A$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1)/($C$2:$C$9<>0),ROWS(E$2:E2))),"")
3	1	3	8364		1	3	8364
4	1	4	0		2	5	8763
5	2	1	0		2	8	8310
6	2	5	8763		3	10	41367
7	2	8	8310		3	12	45171
8	3	10	41367
9	3	12	45171

[Rtlane]

Thanks FlameRetired. Let me explain a little more.

Here is the first table.

id	object	channel
1	2	8665
1	3	8364
1	4	0
2	1	0
2	5	8763
2	8	8310
2	10	41367
3	12	45171

My second table, which will have the formulas, has already been created prior for id 2 column. I will have the object 2 and channel 2 columns blank and will already of had column id 2 filled out before. The table will already contain the correct amount of rows for the id's that dont have a 0 for the channel column. So if the first table table has 2 non-zero channels rows for an id of 1, then the second table will only have 2 rows for an id of 1. This is created already in my spreadsheet. I don't need the id 2 column filled out by a formula. Now keep in mind if the second table doesn't have for example an Id of 3, then it wont be included. As you can see in the first table I have info for an id of 3 but since my second table doesn't have that Id in it, then it wont be included.

id 2	object 2	channel 2
1	formula	formula
1	formula	formula
2	formula	forumla
2	forumla	forumla

This is what my final table should like like with columns "object 2" and "channel 2" filled out by formula.

id 2	object 2	channel 2
1	2	8665
1	3	8364
2	5	8763
2	8	8310

Hopefully that explains what I'm needing a little more.

[FlameRetired]

Aha. OK. In that case please try this:

	A	B	C	D	E	F	G	H
1	id	object	channel		id 2	object 2	channel 2
2	1	2	8665		1	2	8665	In F2:G5 : =INDEX(B$2:B$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1)/(($E2=$A$2:$A$9)*($C$2:$C$9<>0)),COUNTIF($E$2:$E2,$E2)))
3	1	3	8364		1	3	8364
4	1	4	0		2	5	8763
5	2	1	0		2	8	8310
6	2	5	8763
7	2	8	8310
8	3	10	41367
9	3	12	45171

[Rtlane]

Awesome! Thank you so much that worked perfectly.

[FlameRetired]

Good deal. You are welcome. Glad to help and thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.