comparing 3 ranges and return values that are missing based on 2 criteria

flashgordie

Board Regular
Joined
Jan 9, 2008
Messages
95
Office Version
  1. 365
Platform
  1. Windows
Hello,

Further to my post on July 27-2022 (After comparing 2 ranges, return values that are missing based on criteria)

I am hoping for some help on expanding that effort. I would like to add a second criterial (I believe it would be an “and” to capture only the items that have a value in column G > 0.However, when I try to add it, I get an error.

Looking for a formula(s) to start at A20:B?? based on the "#" , that will list the items that appear in D6:E28, that do not appear in A3:B9, as well as that have a category = "Colors" AND have a Value >0

I tried these two ideas, but I feel I’m missing the obvious…. something like… =FILTER(D3:E28,(and(g3:g28>0,(F3:F28="colors"))*(ISNA(MATCH(D3:D28,A3:A9,0))))) or =FILTER(D3:E28,(g3:g28>0,(F3:F28="colors")*(ISNA(MATCH(D3:D28,A3:A9,0)))))

Any help again would be greatly appreciated.

formula for missing items in two lists.xlsx
ABCDEFGHIJKLMNOPQ
1Workbook 1 listWorkbook 2 listThis works….
2#Desc#DescCategoryValuewhere I4 =FILTER(D3:E28,(F3:F28="colors")*(ISNA(MATCH(D3:D28,A3:A9,0))))
31212Red1011WhiteColors0
41314Violet2801DonkeyAnimals01011White
51415orange1212RedColors01819Purple
61516Yellow5551BikeVehicles01014Black
71617Green2809ZebraAnimals0
81718Blue1516YellowColors0
95552TrainVehicles0This needs helps…..
105557TractorVehicles0
112802ElephantAnimals0Where I17 would include an "and" to capture only items with values >0
121718BlueColors0something like… =FILTER(D3:E28,(and(g3:g28>0,(F3:F28="colors"))*(ISNA(MATCH(D3:D28,A3:A9,0)))))
135556BusVehicles0or =FILTER(D3:E28,(g3:g28>0,(F3:F28="colors")*(ISNA(MATCH(D3:D28,A3:A9,0)))))
141617GreenColors0
151819PurpleColors1Should return…
162803PigAnimals0
172805CowAnimals01819Purple
182804SheepAnimals01014Black
195553TruckVehicles0
202806DogAnimals0
211014BlackColors1
225554MotorcycleVehicles0
232808HorseAnimals0
242807CatAnimals0
255555CarVehicles0
262810GoatAnimals0
271415orangeColors0
281314VioletColors0
29
Sheet1
Cell Formulas
RangeFormula
I4:J6I4=FILTER(D3:E28,(F3:F28="colors")*(ISNA(MATCH(D3:D28,A3:A9,0))))
Dynamic array formulas.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
How about
Excel Formula:
=FILTER(D3:E28,(F3:F28="colors")*(ISNA(MATCH(D3:D28,A3:A9,0)))*(G3:G28>0))
 
Upvote 0
@Fluff .... Again, that look very promising. Thank you so much. You assistance is truly appreciated.
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

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