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

Question

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

State	Red	Green	Blue	Yellow
AK	1	3	5	1
AZ	2	6	8	1
AL	7	5	9	5

Sample Sheet 2

AK
AZ
AL

Expected Result

AK	Red, Yellow
AZ	Yellow
AL	Green, Yellow

hansvogelaar · Answer

Tubolard&nbsp;
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.

tubolard · Answer

HansVogelaar&nbsp;&nbsp;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."&nbsp;I don't know what other formula to use or how to include it here to work around that.&nbsp;&nbsp;

patrick2788 · Answer

Tubolard&nbsp;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)
)&nbsp;&nbsp;

Forum Discussion

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

Share

Resources