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)
[TABLE="width: 611"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]



A3
Postcode[/TD]
[TD]



B3
Delivery charge [/TD]
[TD]



C3
Suburbs[/TD]
[TD]


D3[/TD]
[TD]


E4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2481[/TD]
[TD]154[/TD]
[TD]brokean head[/TD]
[TD] byron bay[/TD]
[TD] ewingsdale[/TD]
[/TR]
[TR]
[TD]2482[/TD]
[TD]154[/TD]
[TD]goonengerry[/TD]
[TD] huonbrook[/TD]
[TD] main arm[/TD]
[/TR]
[TR]
[TD]2483[/TD]
[TD]154[/TD]
[TD]billinudgel[/TD]
[TD] brunswick heads[/TD]
[TD] burringbar[/TD]
[/TR]
[TR]
[TD]2484[/TD]
[TD]154[/TD]
[TD]mount warning[/TD]
[TD] murwillumbah[/TD]
[TD] tomewin[/TD]
[/TR]
[TR]
[TD]2485[/TD]
[TD]110[/TD]
[TD]tweed heads[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2486[/TD]
[TD]110[/TD]
[TD]banora point[/TD]
[TD] bilambil[/TD]
[TD] bungalora[/TD]
[/TR]
[TR]
[TD]2487[/TD]
[TD]110[/TD]
[TD]casuarina[/TD]
[TD] chinderah[/TD]
[TD] cudgen[/TD]
[/TR]
[TR]
[TD]2488[/TD]
[TD]110[/TD]
[TD]bogangar[/TD]
[TD] cabarita beach[/TD]
[TD] tanglewood[/TD]
[/TR]
[TR]
[TD]2489[/TD]
[TD]110[/TD]
[TD]hastings point[/TD]
[TD] pottsville[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2490[/TD]
[TD]110[/TD]
[TD]tumbulgum[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4000[/TD]
[TD]77[/TD]
[TD]Brisbane[/TD]
[TD] Petrie Terrace[/TD]
[TD] Spring Hill[/TD]
[/TR]
[TR]
[TD]4001[/TD]
[TD]77[/TD]
[TD]Brisbane[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4002[/TD]
[TD]77[/TD]
[TD]City East[/TD]
[TD] Wintergarden[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.