Help with lookups; looking up text, finding a match & returning the corresponding value from another column

[swinno14]

Hi guys, I have a bit of a tricky one that I cant work out.

i'm trying to create a lookup that looks up a text field (suburb name) and then goes searching in a table for that matching suburb name, once it finds it it then returns the value in column 1 (the postcode)

as you can see below the suburbs, span across multiple columns, this is to keep each post code in line with the suburbs

Lookup Cell value (A1): ewingsdale
Forumla inside result cell, that keeps giving me a #N/A (B1): =VLOOKUP(A1,A5:E17,1,FALSE)

A3 Postcode	B3 Delivery charge	C3 Suburbs	D3	E4
2481	154	brokean head	byron bay	ewingsdale
2482	154	goonengerry	huonbrook	main arm
2483	154	billinudgel	brunswick heads	burringbar
2484	154	mount warning	murwillumbah	tomewin
2485	110	tweed heads
2486	110	banora point	bilambil	bungalora
2487	110	casuarina	chinderah	cudgen
2488	110	bogangar	cabarita beach	tanglewood
2489	110	hastings point	pottsville
2490	110	tumbulgum
4000	77	Brisbane	Petrie Terrace	Spring Hill
4001	77	Brisbane
4002	77	City East	Wintergarden

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

I've tried formatting all the lookup fields & the whole table to TEXT, but that didnt change anything.

any help would be greatly appreciated, thank you in advance guys!

 

[Aladin Akyurek]

Ah ok, well i've managed to get around my issue by just moving all the suburb names into one column and using the MATCH formula you gave me... quite a tedious process but at least its working now.

Thank you for your help i really appreciate it!!

Great. Thanks for the update.