Extract non zero value data

**paraxis** · 03-13-2013, 09:09 AM

Hi All,
I have a worksheet which contains a very long list of part numbers with various columns for models which use the parts.

I would like that when I enter a model name in a cell, excel will extract a list of product codes which are used in that particular model. Is there a way to solve this problem without any VBA. I have tried with sumif and sumproduct but I am still getting the zero value cells.

To make it simple I have attached a sample copy Xl workbook.
I use Excel 2003.

Thanks guys
paraxis

**Melvinrobb** · 03-13-2013, 09:19 AM

Is the attached what you are looking for?
Change the model in cell I4 and the list of products changes accordingly.

**benishiryo** · 03-13-2013, 09:25 AM

hi paraxis, it would be good if you can update your profile's Excel version. i'm avoiding IFERROR because i don't know your version. so maybe this array formula in "MODEL MARY" or other sub worksheets B6:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

then in C6:
=VLOOKUP(B6,Sheet1!$B$6:$F$26,MATCH($C$4,Sheet1!$B$4:$F$4,0),0)

copy down

**Melvinrobb** · 03-13-2013, 09:28 AM

Because you need to have a tab for each model, that solution will not work. A pivot table should give you what you want though.

**paraxis** · 03-13-2013, 09:41 AM

Dear Melvinrobb,
It works even for tab for each model. I cut from master sheet and pasted it on a model sheet tab and it worked.

Thanks a lot everyone. I knew it was not that complicated but somehow I could not figure it out.

Regards to all
Paraxis