Comparing two ranges and returning a value from a third range

[cologeek]

Hello,

I have a row with ten cells populated with names on one worksheet (columns B-K). One a second worksheet I have several hundred names in column A and values next to them in column B.

Is it possible to compare the names in the row against the names on the second worksheet and return the lowest value?

I would like to put a formula next to each row (in column A on the first worksheet) that reads across the row and returns the lowest value.

I have tried several array formulas and have been unable to figure out how to do this.

Thank you in advance for your help!

 

[barry houdini]

Are the several hundred names in column A all different?

If so perhaps try this formula in A2

=MIN(SUMIF(sheet2!A$1:A$500,B2:K2,Sheet2!B$1:B$500))

confirmed with CTRL+SHIFT+ENTER and copied down

 

[cologeek]

Thank you Barry. It doesn't seem to be working as I'm getting zeros as a result - even after I apply the ctrl-shift-enter.

 

[barry houdini]

Is B2:K2 fully populated, does each name have a value associated with it?

If not you could try ignoring zeroes with this amendment

=MIN(IF(SUMIF(Sheet2!A$1:A$500,B2:K2,Sheet2!B$1:B$500)<>0,SUMIF(Sheet2!A$1:A$500,B2:K2,Sheet2!B$1:B$500)))

although that would ignore zero when it might be a legitimate result......

 

[cologeek]

Hi Barry - It was the zeros! Thank you so much for your expertise!