CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaminarray-formulasgs-vlookup
TC76
TC76

Reputation: 870

Search columns for lowest value (in each row) and return header and value

I want to search a range for the lowest price of an item in a row and return that price as well as the store (header). Ideally, this would be an ARRAYFORMULA as the list will be probably grow constantly.

I've tried a combination of VLOOKUP and MATCH, but just can't seem to get it to all come together.

enter image description here

Here's a link to my sheet.

Upvotes: 1

Views: 709

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(A2:G),
 "select "&TEXTJOIN(",", 1, IF(LEN(A2:A), 
 "min(Col"&ROW(A2:A)-ROW(A2)+1&")", ))&"")),
 "select Col2"))

enter image description here

and headers:

=ARRAYFORMULA(IFNA(VLOOKUP(I2:I, SPLIT(TRANSPOSE(SPLIT(
 QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(B2:D="",,"♠"&B2:D&"♦"&B1:D1))
 ,,99^99)),,99^99), "♠")), "♦"), 2, 0)))

Upvotes: 2

Related Questions