Excel Magic Trick 1120: Is Item in All Three Lists?

preview_player
Показать описание


See how to create a Logical Formula that will check whether an item in in all three lists using 2 MATCH functions and the ISNUMBER function.
  1. excelisfun
  2. Excel 2013
  3. Excel 2003
  4. Excel 2007
  5. Excel 2010
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:02:23

Excel Magic Trick 1120: Is Item in All Three Lists?

Excel Magic Trick 0:05:11

Excel Magic Trick 1111: Item In Both Lists? Extract With Better Array Formula

Excel Magic Trick 0:05:21

Excel Magic Trick 1119: Conditional Format Date when 44 Days Have Passed

Excel Magic Trick 0:05:12

Excel Magic Trick 1108: Compare 2 Lists and Extract Records: Filter Method

Excel Magic Trick 0:13:06

Excel Magic Trick 1118: MODE for Text Values, Extract Word That Occurs Most Frequently

Excel Magic Trick 0:05:10

Excel Magic Trick 999: MATCH Rather Than OR function to Conditionally Format Row With OR Criteria

Excel Magic Trick 0:11:57

Excel Magic Trick 1122: Repeat Row Headers Vertically For Each Day Activity Exists: Array Formula

Excel Magic Trick 0:03:04

Excel Magic Trick 1105: Minimum With Two Criteria: When NOT to use Array Formula: DMIN

Excel Magic Trick 0:01:13

Excel Magic Trick 1115: PivotTable to Count How Many of Each Item There Are In a Column

Excel Magic Trick 0:04:44

Excel Magic Trick 1123: Dynamic Formula To Create A List of Dates For Month

Excel Magic Trick 0:05:17

Excel Magic Trick 1109: Compare 2 Lists and Extract Records: Advanced Filter Method

Excel Magic Trick 0:07:51

Excel Magic Trick 1112: Clean Transactional Data, Then Create PivotTable Monthly Cost Report

Excel Magic Trick 0:19:49

Excel Magic Trick 1285: OR Criteria & AND Criteria for Max & Min Calculations (5 Examples)

Excel Magic Trick 0:03:23

Excel Magic Trick 1132: Extract and Sort Numbers Greater Than Zero with Formula

Excel Magic Trick 0:14:22

Excel Magic Trick 1104: Add with 6 Criteria (AND and OR Criteria) with Criteria/Data Mismatch

FILTERXML Function to 0:05:08

FILTERXML Function to Split Text into Cells. Excel Magic Trick 1685.

Excel Magic Trick 0:06:51

Excel Magic Trick 1130: Aging Accounts Receivable Reports Using Advanced Filter & Page Setup

Excel Magic Trick 0:06:30

Excel Magic Trick 1114: Formula For Sequential & Repeating Numbers: 18400 1, 18441 1, 18442 2...

Excel Magic Trick 0:11:03

Excel Magic Trick 1117: Calculate Average For Each Sample: AVERAGEIFS, PivotTable, or OFFSET

Password protected pencil 0:00:16

Password protected pencil box with pinball game|2 in 1 pencil case| #stationery #craft #shorts #code

Excel Functions_ Using 0:01:40

Excel Functions_ Using the Nested IF Function

Remove Country Code 0:00:26

Remove Country Code From Phone number - Right Function - Len Function in excel

gas pe khade 0:00:16

gas pe khade ho gaye ladki | vj pawan singh | shorts

Money In Excel 0:18:34

Money In Excel - Categories & Subcategories Expense Items 1120

Комментарии
Автор

Excel Magic Trick 1120: Is Item in All Three Lists? 

excelisfun
Автор

Simple, yet effective solution. Thanks!

pmsocho
Автор

short and simple, yet totally effective!!! awesome

elphau
Автор

Mike, Love your Ctrl-Shift-Enter book.  My array formula:
- returns 1 if in all lists.
Keep up the great work!!!!

MichaelVB
Автор

Thanks so much for this video Mike. Watching your videos has really opened up a new world for me....I'm doing a campus internship right now and for two days last week I'd been solving this problem by using Ctrl+F for each cell! D: 

elodieliu
Автор

very nice! thanks, Mike ~ you posted this the same time I posted my video! ... give or take 10 minutes ;) ~

LearnAccessByCrystal
Автор

Hi Mike
A very simple solution:
=COUNTIF($A$2:$C$8, E2)=2
If the ranges are not adjacent or on different sheets:
=(COUNTIF($A$2:$A$8, E2)*COUNTIF($C$2:$C$7, E2))=1

XLarium
Автор

Logical and simple ;)) Thanks
A little more difficult
=ISLOGICAL(IF(MATCH(E2, $A$2:$A$8, 0), MATCH(E2, $C$2:$C$7, 0)<0, ""))

Regards

BillSzysz