Return cells below lookup value

[qontrol]

Hi everyone

I'm having a probleming with following:

In the following excel table i would like to extract the Materials that belong to certain Models. E.g:

[TABLE="width: 141"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Models[/TD]
[TD]Materials[/TD]
[/TR]
[TR]
[TD="align: right"]1012555[/TD]
[TD="align: right"]1012555[/TD]
[/TR]
[TR]
[TD="align: right"]1012556[/TD]
[TD="align: right"]1012555001[/TD]
[/TR]
[TR]
[TD="align: right"]1012557[/TD]
[TD="align: right"]1012555002[/TD]
[/TR]
[TR]
[TD="align: right"]1012558[/TD]
[TD="align: right"]1012555003[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012556[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012556001[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012556002[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012556003[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012556004[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012557[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012557001[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012557002[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012557003[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1012558[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012558001[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012558002[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]1012558003[/TD]
[/TR]
</tbody>[/TABLE]

I would like to lookup the values that are below the modelnumber in the 2nd column. Example: If i lookup 1012555 in the 1st column, i want to return 1012555001, 1012555002, 1012555003 which are in the 2nd column, but not the next modelnumber 1012556. Is there any function to look this up?

I have found that the index function (+1 trick) helps, but it will only give me 1 value instead of 3 which is what i want.

Can someone help me?

Thanks!

 

[JoeMo]

This assumes your materials will always be represented by a 10-digit number. This is an array formula. Select a range in column E that is longer than the largest number of materials in any model. In the example below, the array formula was entered in E2:E10 using ctrl + shift + enter.
Sheet2

*	A	B	C	D	E
Models	Materials	*	Model
*	Materials
*	*
*	*
*	*	#N/A
*	*	*	#N/A
*	*	*	#N/A
*	*	*	#N/A
*	*	*	#N/A
*	*	*	#N/A
*	*	*	*
*	*	*	*
*	*	*	*
*	*	*	*
*	*	*	*
*	*	*	*
*	*	*	*
*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:84px;"><col style="width:64px;"><col style="width:65px;"><col style="width:77px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]1012555[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1012555[/TD]
[TD="align: right"]1012555[/TD]

[TD="align: right"]1012555001[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1012556[/TD]
[TD="align: right"]1012555001[/TD]

[TD="align: right"]1012555002[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]1012557[/TD]
[TD="align: right"]1012555002[/TD]

[TD="align: right"]1012555003[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]1012558[/TD]
[TD="align: right"]1012555003[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1012556[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]1012556001[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1012556002[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]1012556003[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]1012556004[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]1012557[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]1012557001[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]1012557002[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]1012557003[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]1012558[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]1012558001[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]1012558002[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]1012558003[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 {=OFFSET(INDEX($A$2:$B$18,MATCH(E1,$B$2:$B$18,0),2),1,0,SUM(IF((MOD($B$2:$B$18,E1)<10)*(LEN($B$2:$B$18)=10),1,0)),1)} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[qontrol]

Wow thank you so much, this works!! How do i get rid of the N/A's? I put the IFNA function in front but it does not seem to work...