Lookup with Exclusion List formula

G

ghrain22

Active Member
Joined
Jan 9, 2014
Messages
473
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:
[TABLE="class: grid, width: 225"]
<tbody>[TR]
[TD]#[/TD]
[TD]type[/TD]
[TD]fruit[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]nectarine[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]naval[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]tangerine[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]clementine[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]mcintosh[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]fuji[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]honey crisp[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]gold and delicious[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]honeydew[/TD]
[TD]melon[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]water melon[/TD]
[TD]melon[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]canteloup[/TD]
[TD]melon[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Exclusion List:
[TABLE="class: grid, width: 225"]
<tbody>[TR]
[TD]type[/TD]
[/TR]
[TR]
[TD]nectarine[/TD]
[/TR]
[TR]
[TD]mcintosh[/TD]
[/TR]
[TR]
[TD]water melon[/TD]
[/TR]
</tbody>[/TABLE]


Output:[TABLE="class: grid, width: 225"]
<tbody>[TR]
[TD]fruit[/TD]
[TD]output[/TD]
[TD]should return[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]{formula}[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]honey crisp[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]melon[/TD]
[TD]{formula}[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]canteloupe[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]{formula}[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]tangerine[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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
 
Sort by date Sort by votes
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))
 
Upvote 0
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...


[TABLE="width: 140"]
<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>[TR]
[TD="class: xl63, width: 83, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 104, bgcolor: white"]output[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 83, bgcolor: white"]apple[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]honey crisp[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 83, bgcolor: white"]melon[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]tangerine[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 83, bgcolor: white"]orange[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]clementine[/TD]
[/TR]
</TBODY>[/TABLE]

B2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=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).
 
Upvote 0
Aladin Akyurek said:
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...


[TABLE="width: 140"]
<TBODY>[TR]
[TD="class: xl63, width: 83, bgcolor: white"]fruit
[/TD]
[TD="class: xl63, width: 104, bgcolor: white"]output
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 83, bgcolor: white"]apple
[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]honey crisp
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 83, bgcolor: white"]melon
[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]tangerine
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 83, bgcolor: white"]orange
[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]clementine
[/TD]
[/TR]
</TBODY>[/TABLE]

B2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=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).
Click to expand...

Correction...

[TABLE="width: 154"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5006" width=141><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: white"]fruit[/TD]
[TD="class: xl65, width: 141, bgcolor: white"]output[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]apple[/TD]
[TD="class: xl66, width: 141, bgcolor: white"]honey crisp[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]melon[/TD]
[TD="class: xl66, width: 141, bgcolor: white"]canteloup[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]orange[/TD]
[TD="class: xl66, width: 141, bgcolor: white"]clementine[/TD]
[/TR]
</TBODY>[/TABLE]

Rich (BB code): 
=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))))
 
Upvote 0
Aladin Akyurek said:
Note that the result for orange is clementine (num = 2), not tangerine (num = 4).
Click to expand...

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):

Code: 
[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]
 
Upvote 0
ghrain22 said:
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):

Code: 
[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]
Click to expand...

That's great. Thanks for providing feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Lookup a Column Range by Matching Heading
Replies
3
Views
425
jcoeng
J
T
  • Solved
Lookup across multiple dynamic array columns
Replies
2
Views
2K
Tashat
T
D
  • Solved
Extract multiple specified values from cell and return in 1 cell
2
Replies
14
Views
2K
Amit Tandon
A
C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
731
fjns
F
G
  • Question Question
Merge Two Tables and Convert to Data List
2
Replies
15
Views
2K
Gimics
G

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top