CODEWITHSUNDEEP
google-sheetsminarray-formulasflattengoogle-query-language
Дмитрий Городничин
Дмитрий Городничин

Reputation: 75

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

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?

Upvotes: 1

Views: 761

Answers (1)

player0
player0

Reputation: 1

try:

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

enter image description here

update:

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

enter image description here

Upvotes: 2

Related Questions