Mar 31 2022 01:24 PM
Hi All,
I'm having trouble locating the error in my formula. I have a data sheet '012021' where column E is a list of locations (some duplicated) and f-g a various number values. The duplicate cities have erroneous data attached, so I only need the rows that have the largest value in column f.
Cleaning up the data and removing duplicates isn't an option as the file is just too large.
Finding the value of the matching city in column f is easy D3 is Philadelphia, PA for this example:
=MAX(IF('012021'!E:E=D3,'012021'!F:F)
Yet when I try to find G, I get a spill error:
=IF('012021'!E:E=C6,IF('012021'!F:F=D6),'012021'G:G))
I even tried changing the return value to a single string and I'm still getting a SPILL error.
=IF('012021'!E:E=C6,IF('012021'!F:F=D6),"hey it finally worked"))
What am I missing?
Mar 31 2022 01:33 PM
If you enter that formula in any cell in first row, it returns an array of 1048576 rows with result.
If in any other row it returns #SPILL! error since there is not enough space to place more than one million of values.
Not sure what you try to return exactly, in any case use ranges, tables or dynamic ranges, not entire column in calculations.
By the way, more correct syntax
=IF('012021'!E:E=C6,IF('012021'!F:F=D6,'012021'!G:G,))
Mar 31 2022 01:58 PM
Mar 31 2022 02:09 PM
Latest formula also returns 1 million values array in each element of it we have text if condition is met for related row in columns E and F, otherwise FALSE.
=IF( column, something) returns as many elements as you have them in entire column.
Apr 01 2022 07:55 AM
SolutionApr 01 2022 07:55 AM
Solution