Lookup from block of data - return from set column

[PaulyH]

Hi

I'm trying to look up data from a table of data (multiple rows and columns) and when that data is found I want to return the data in column A on the line that data is found to column B (in green) on another sheet. I have highlighted the column in green, in yellow is the example. 'South Hams' would be what would be returned to cell C6.

 

[Fluff]

What version of Excel are you using?
Please update you account details to show this, as it affects which functions you can use.

 

[Fluff]

If you are using 2010 or newer, try

=INDEX(Sheet2!$A$2:$A$200,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$200)-ROW(Sheet2!$A$2)+1)/(Sheet2!$B$2:$I$200=B2),1))