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

Copper Contributor

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

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
3 Replies

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

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.

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

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.

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

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

365 solution

``````=LET(
GetColors, LAMBDA(row,