Return header of Min value from a range, where first cell in row matches search key.

Copper Contributor

Hello I need help putting together a formula that solves the following for me.

 

I would like to reference "Sample Sheet 1" using a search key from "Sample Sheet 2" to attach the header of each column in the range that contains the minimum value.

 

In example: If the formula is in cell B1 of "Sample Sheet 2" It will find cell A2 on "Sample Sheet 1" as it matches "AK", identify B2 and E2 as the minimum, and return headers titled "Red, Yellow".

 

Sample Workbook attached, thank you!

 

Sample Sheet 1

StateRedGreen BlueYellow
AK1351
AZ2681
AL7595

 

Sample Sheet 2

AK

 
AZ 
AL 

 

Expected Result

AKRed, Yellow
AZYellow
ALGreen, Yellow
3 Replies

@Tubolard 

In B1:

=TEXTJOIN(", ", TRUE, FILTER('Sample Sheet 1'!B$1:E$1, 'Sample Sheet 1'!B2:E2=MIN('Sample Sheet 1'!B2:E2)))

Fill down.

@Hans Vogelaar 

 

Hello, I would need the formula to be able to choose which row to evaluate for the min based off of the state name. Is there a way to do that with the filter formula? Any way that I tried to set the filter up to evaluate the full range would return the error "FILTER range must be a single row or a single column."

 

I don't know what other formula to use or how to include it here to work around that.

 

 

@Tubolard 

This formula doesn't assume the states are in the same order in both sheets.

365 solution

=LET(
    GetColors, LAMBDA(row,
        LET(lowest, MIN(row), headings, FILTER(colors, row = lowest), TEXTJOIN(", ", , headings))
    ),
    Results, BYROW(values, GetColors),
    XLOOKUP(each_state, states, Results)
)