return first value in column containing search term

[brewben]

Hi,

I have two tabs in my workbook - first one is Spare Laptops, and the second is UK towns.

In the Office (A) column, I'd like to input a town, and the region/column that town is found in on the 'uk towns' tab, ie B1, C1, D1, E1, F1, G1 is then the result.

Simply put - enter, eg. 'Elgin' in Office, and 'Scotland' will be in Region, etc.

It took me a while, but I've managed to get it to at least check every column to see if the town is listed, but it simply returns the town as the region, not the actual region.

I've used this formula to get this far:

=IFERROR(INDEX('UK Towns'!B:B,MATCH(A2,'UK Towns'!B:B,)),IFERROR(INDEX('UK Towns'!C:C,MATCH(A2,'UK Towns'!C:C,0)),IFERROR(INDEX('UK Towns'!D:D,MATCH(A2,'UK Towns'!D:D,0)),IFERROR(INDEX('UK Towns'!E:E,MATCH(A2,'UK Towns'!E:E,0)),IFERROR(INDEX('UK Towns'!F:F,MATCH(A2,'UK Towns'!F:F,0)),IFERROR(INDEX('UK Towns'!G:G,MATCH(A2,'UK Towns'!G:G,0)),"Town not listed, please check again"))))))

Hope this makes sense.

Thanks in advance.

Bruce

 

[brewben]

That sounds as though some of the towns have leading/trailing spaces, or other hidden characters.

I will investigate - that does make sense. I will now learn how to use the AGGREGATE function, it seems very powerful.

 

[Fluff]

Glad to help & thanks for the feedback.