Google Sheets - Find the lowest values in the entire table line by line

Question

I can't find a way to make ArrayFormula and SMALL functions work together using the built-in Google sheets functions.

I have a table1 with items and their prices. Each position has the lowest price with SMALL function, but when you add a new row, the formula breaks.

If you roughly add an ArrayFormula (table2), SMALL looks for the lowest value in the entire table, not line by line.

I've tried different combinations of VLOOKUP, ROW, INDEX, but can't solve the puzzle.

Does anyone have any ideas?

Answer 1

try:

=INDEX(TRANSPOSE(QUERY(TRANSPOSE(D14:O); 
 "select "&TEXTJOIN(","; 1; IF(B14:B="";;
 "min(Col"&ROW(B14:B)-ROW(B14)+1&")"))));; 2)

---

update:

=INDEX(QUERY(SPLIT(FLATTEN(ROW(F14:F)&"×"&OFFSET(F14;;;9^9; 9^9)); "×");
 "select min(Col2) 
  group by Col1  
  label min(Col2)''"))