Lookup with Exclusion List formula

[ghrain22]

I'm try to perform a look up and cross reference an exclusion list, and would appreciate any help. I want to return the smallest number that who's lookup value is NOT in the exclusion list.

List:

#	type	fruit
1	nectarine	orange
5	naval	orange
4	tangerine	orange
2	clementine	orange
3	mcintosh	apple
7	fuji	apple
6	honey crisp	apple
9	gold and delicious	apple
10	honeydew	melon
8	water melon	melon
4	canteloup	melon

<tbody>
</tbody>

Exclusion List:

type

nectarine

mcintosh

water melon

<tbody>
</tbody>

Output:

fruit	output	should return
apple	{formula}	honey crisp <tbody> </tbody>
melon	{formula}	canteloupe <tbody> </tbody>
orange	{formula}	tangerine <tbody> </tbody>

<tbody>
</tbody>

This was my attempt through using arrays(although it doesn't work maybe it can be of some inspiration?):

=VLOOKUP(SMALL(IF(IF(M2=INDEX(list,,3,1),INDEX(list,,2,1),"")=exclusion,MAX(INDEX(list,,1,1))+1,INDEX(list,,1,1)),1),list,2,FALSE)

The above formula is using the named ranges for the tables listed above.

Any help would be greatly appreciated!

Thanks!
Ghrain22

 

[BobUmlas]

Your # list should be unique - you have 2 "4"s in it. It you can change the 2nd 4 to maybe 4.0001, then this formula works - ctrl+shift+enter & fill down (I used range names, but I think they're obvious what they refer to):
=INDEX(type,MATCH(MIN(IF(fruit=A19,IF(ISNA(MATCH(type,Exclusion,0)),num))),num,0))

 

[Aladin Akyurek]

Define the following...
num as referring to A2:A12;
type as referring to B2:B12;
fruit as referring to C2:C12.

Let exclusion (as you already defined) refer to list of exclusions.

Let A:B of say Sheet2 house the required processing...

fruit	output
apple	honey crisp
melon	tangerine
orange	clementine

<COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2958" width=83><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3697" width=104><TBODY>
</TBODY>

B2, control+shift+enter, not just enter, and copy down:

=INDEX(type,
  MIN(IF(num=MIN(IF(IF(ISNUMBER(MATCH(type,exclusion,0)),"",fruit)=A2,num)),
  ROW(type)-ROW(INDEX(type,1,1))+1)))

Note that the result for orange is clementine (num = 2), not tangerine (num = 4).

 

[Aladin Akyurek]

Define the following...
num as referring to A2:A12;
type as referring to B2:B12;
fruit as referring to C2:C12.

Let exclusion (as you already defined) refer to list of exclusions.

Let A:B of say Sheet2 house the required processing...

fruit	output
apple	honey crisp
melon	tangerine
orange	clementine

<TBODY>
</TBODY>

B2, control+shift+enter, not just enter, and copy down:

=INDEX(type,
  MIN(IF(num=MIN(IF(IF(ISNUMBER(MATCH(type,exclusion,0)),"",fruit)=A2,num)),
  ROW(type)-ROW(INDEX(type,1,1))+1)))

Note that the result for orange is clementine (num = 2), not tangerine (num = 4).

Correction...

fruit	output
apple	honey crisp
melon	canteloup
orange	clementine

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5006" width=141><TBODY>
</TBODY>

=INDEX(type,MIN(IF(fruit=A2,
  IF(num=MIN(IF(IF(ISNUMBER(MATCH(type,exclusion,0)),"",fruit)=A2,num)),
  ROW(type)-ROW(INDEX(type,1,1))+1))))

 

[ghrain22]

Note that the result for orange is clementine (num = 2), not tangerine (num = 4).

This is why we have Excel do such processes . Thank you both for your help. Both you suggestions inspired me as to how to run through the array properly.

For future readers, Aladin's formula post worked well for this (with ctrl+shft+enter):

[FONT=lucida console]=INDEX(type,MIN(IF(fruit=A2, IF(num=MIN(IF(IF(ISNUMBER(MATCH(type,exclusion,0)),"",fruit)=A2,num)), ROW(type)-ROW(INDEX(type,1,1))+1))))[/FONT]

 

[Aladin Akyurek]

This is why we have Excel do such processes . Thank you both for your help. Both you suggestions inspired me as to how to run through the array properly.

For future readers, Aladin's formula post worked well for this (with ctrl+shft+enter):

[FONT=lucida console]=INDEX(type,MIN(IF(fruit=A2, IF(num=MIN(IF(IF(ISNUMBER(MATCH(type,exclusion,0)),"",fruit)=A2,num)), ROW(type)-ROW(INDEX(type,1,1))+1))))[/FONT]

That's great. Thanks for providing feedback.