Google Sheets ArrayFormula to Compare Multiple Cells from Different Rows
I'm making a checklist of Disney Blu-rays. Some Disney movies are available in 4K but some are not. I'm trying to create a column that will show if a movie release is available in a higher quality than the entry for that row.
Here is a simplified test sheet that should hopefully make my goal more clear... https://docs.google.com/spreadsheets/d/1L2-npiEdTE9ardDi2q38sYhkKRFKAPCu_gtJFbOZa80/edit?usp=sharing
I've found a ton of ways to compare rows, look for duplicates, etc. But despite my limited but best efforts, I can't find a way to combine what I've found in a way that works towards this goal. The best I've been able to do is find duplicate titles but I've not been able to compare that against the Original Theatrical Release Year, and Blu-ray vs 4K release columns.
I'd like to do this through an ArrayFormula so that any rows I add are easily and automatically rendered into the formula without any extra work. Any help would be greatly appreciated! Thank you.
try:
=INDEX(LAMBDA(a, b, c, d, LAMBDA(x, IF(a="",,
IF(IFERROR(x, "No")="No", "Yes", "No")))(VLOOKUP(a&d,
{FILTER(FILTER(a&d, b), NOT(COUNTIF(FILTER(a, c), FILTER(a, b))));
FILTER(FILTER(a&d, c), COUNTIF(FILTER(a, b), FILTER(a, c)))}, 1, )))
(A3:A&B3:B, C3:C="Blu-ray", D3:D="4K", ROW(B3:B)))
- Batch: Comparing version numbers via GEQ when said version uses dots and varying number of digits
- Is there a way I can reduce this in-a-row kind of repeating Lua conditions?
- two if or more with one ELSE
- Simplifying If Statements Into Loop
- Add 95 to a cell if the name and date in that row match another row with the same name and date
- Is there a way to do 24-hour format and 60-minute format in Java?
- Using IF in function for different queries as a result
- Fat-Free Framework Template Conditional Rendering Issue on Template
- Print a multiplication table in Python
- Can you store a variable inside a if-clause?
- How do I test for exceptions in if-statements in python?
- How to write lambda function with three conditions
- Matching multiple number rows and check corresponding dates within 90 days
- Sum all cells for which corresponding cell is not a formula and doesn't return #VALUE AND date occurs in the past
- If condition that matches if grep outputs at least one result even though the exit code is 2 due to some permission denied
- Why does `ifelse()` behave differently than `if (cond) {...} else {...}`?
- A Return inside and outside an If Statement
- How can I use "*ngIf else"?
- How to check repeated letters in a string c#
- How to create an 'empty if statement' in Python
- How to avoid if statement? for the compiler cannot optimize it to simd
- Test if it exists a node HXT
- python else statement causes a continuous loop
- Series of if vs else-if statements benchmarking
- Google Sheets - make a list of the names in column A where column E is greater than zero
- Condition evaluation process in Java
- Excel - Array Formula to skip empty cells
- Groups of compound conditions in Bash test
- How to use the IF statement to avoid the Weather API fetch result crashing once the user types wrong the city name?
- Comparing Text with a lot of If/else