Doing a look up on a cell with comma separated values

Hi,

Array formula**

=AVERAGE(IF(ISNUMBER(SEARCH(", "&Sheet2!A1:A6&", ",", "&A1&", ")),Sheet2!B1:B6))


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Originally Posted by tim201110

=AVERAGE(IF(ISNUMBER(FIND(A1:A6,F1)),B1:B6)) as array formula

True, it's quite unlikely that such an approach will fail, though not impossible. For example, assuming that F1 contains:

"United States, Canada, Nigeria"

and that the list in A1:A6 contains "Niger" amongst its entries, your formula will give incorrect results.

Hence the reason why I included the commas in my solution.

Regards