Hello,

I would like to take a comma separated list of entities in a single cell, do a lookup of their corresponding scores on a separate table, and then average the scores that are returned for each entity.

Example:

Cell 1A, Sheet 1: United States, Canada, India (all in one cell)

Table 2, Sheet 2, columns A and B:
United States- 5
Canada- 6
United Kingdom- 10
Egypt- 3
Lebanon- 2
India- 7

So I'd want cell 2A in Sheet1 to populate with 6, the average of 5,6,7. Is there any way this can be done? I don't want to do text to columns here. Any other solution?