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

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[brewben]

Thank you for the nudge - I have now updated my profile - Office pro 2016.

 

[Fluff]

Thanks for that
How about

=INDEX(Sheet1!$1:$1,AGGREGATE(15,6,COLUMN(Sheet1!$B$1:$N$1)/(Sheet1!$B$2:$N$100=A2),1))

 

[brewben]

Thanks for your speedy reply. I have substituted 'sheet1' with UK Towns - but get the error #NUM!

I suspect I haven't tailored your equation correctly?


=INDEX('UK Towns'!$1:$1,AGGREGATE(15,6,COLUMN('UK Towns'!$B$1:$G$1)/('UK Towns'!$B$2:$G$100=A2),1))

 

[Fluff]

Does the value in A2 exactly match one of the towns on the UK Towns sheet?

 

[brewben]

Does the value in A2 exactly match one of the towns on the UK Towns sheet?

It does - I've tried several towns - still the error

 

[brewben]

Hang on - I used copy and paste - seems to be working... i'll try some more

 

[brewben]

So, this is great! It will work if the town is copied and pasted, but not if it is typed in directly? If necessary, I'll create a drop down list for each cell, if that's what it will need?

EDIT: It now seems to be working fine! Not sure why it wouldn't have the town typed in, but now it seems to be ok when i typed other towns. Hugely grateful for your help. Thanks.

 

[Fluff]

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