# Comparing two ranges and returning a value from a third range

#### cologeek

##### New Member
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!

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### barry houdini

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

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

Replies
8
Views
2K
Replies
5
Views
223
Replies
1
Views
465
Replies
0
Views
658
Replies
7
Views
998

1,190,564
Messages
5,981,704
Members
439,731
Latest member
auraitsuka

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back