Back
Post
Rewrote some convoluted spreadsheet math that was breaking
One of the columns in the spreadsheet has some quite detailed computations, and I was spinning my wheels before finally solving it. I'm not a spreadsheet genius, so getting this sorted was an absolutely HUGE deal.
=ARRAYFORMULA(
IF(
A2:A<>"",
IF(
COUNTIF(Items!A:A, A2:A)>1,
IF(
VLOOKUP(
A2:A,
QUERY({Items!A:A, Items!E:E, Items!F:F, Items!G:G, Items!H:H}, "SELECT Col1, MAX(Col2), Col3, Col4 WHERE Col1 IS NOT NULL GROUP BY Col1, Col3, Col4", 1),
2,
FALSE
) = VLOOKUP(
A2:A,
QUERY({Items!A:A, Items!E:E, Items!F:F, Items!G:G, Items!H:H}, "SELECT Col1, MIN(Col2), Col3, Col4 WHERE Col1 IS NOT NULL GROUP BY Col1, Col3, Col4", 1),
2,
FALSE
),
"All prices the same",
"$" & TEXT(
VLOOKUP(
A2:A,
QUERY({Items!A:A, Items!E:E, Items!F:F, Items!G:G, Items!H:H}, "SELECT Col1, MAX(Col2), Col3, Col4 WHERE Col1 IS NOT NULL GROUP BY Col1, Col3, Col4", 1),
2,
FALSE
),
"#,##0.00"
) &
" per " &
VLOOKUP(
A2:A,
QUERY({Items!A:A, Items!E:E, Items!F:F, Items!G:G, Items!H:H}, "SELECT Col1, MAX(Col2), Col3, Col4 WHERE Col1 IS NOT NULL GROUP BY Col1, Col3, Col4", 1),
3,
FALSE
) &
" " &
VLOOKUP(
A2:A,
QUERY({Items!A:A, Items!E:E, Items!F:F, Items!G:G, Items!H:H}, "SELECT Col1, MAX(Col2), Col3, Col4 WHERE Col1 IS NOT NULL GROUP BY Col1, Col3, Col4", 1),
4,
FALSE
)
),
"N/A"
),
""
)
)
👋 Join WIP to participate