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!

 

[swinno14]

Oh and, before you ask, yes these are actually all real suburb names from SouthEast Queensland, Australia.

Most of them are aboriginal names, Tumbulgum is one of my favorites

 

[TLeft21]

If you have a list of specific suburbs in another sheet, and you want to extract the column A data, just do an =IF(statement) to say =IF((specified suburb cell)=C1,A1,"",1)... I was having trouble understanding the request, but let me know if this helps

 

[Aladin Akyurek]

In B1 enter:

=INDEX($A$4:$A$16,MATCH(A1,$E$4:$E$16,0))

 

[swinno14]

That would be a really big long IF formula with a different IF statement for each suburb wouldnt it?? if so that wont work for me as I have over 200 suburbs. thank you though

 

[swinno14]

In B1 enter:

=INDEX($A$4:$A$16,MATCH(A1,$E$4:$E$16,0))

Hey Aladin, this is working fine for that 1 column (E4:E16) but as soon as i try to drag the array over more than 1 column it doesnt work (returns #N/A) any idea how to get around that? or have i reached the limitations of what the "MATCH" formula can do?


thank you

 

[Aladin Akyurek]

Hey Aladin, this is working fine for that 1 column (E4:E16) but as soon as i try to drag the array over more than 1 column it doesnt work (returns #N/A) any idea how to get around that? or have i reached the limitations of what the "MATCH" formula can do?


thank you

Care to elaborate on what you mean by dragging over more than 1 column?

 

[swinno14]

Care to elaborate on what you mean by dragging over more than 1 column?

so that formula works for matching values in column E, but what about if I want to match across multiple columns? I would change the formula to this: =INDEX($A$4:$A$16,MATCH(A1,$C$4:$E$16,0))

but when I do this it comes up with a #N/A result again.

 

[Aladin Akyurek]

so that formula works for matching values in column E, but what about if I want to match across multiple columns? I would change the formula to this: =INDEX($A$4:$A$16,MATCH(A1,$C$4:$E$16,0))

but when I do this it comes up with a #N/A result again.

MATCH needs one dimensional input like C4:E4, E2:E16, etc., not something like C4:E16.

 

[swinno14]

MATCH needs one dimensional input like C4:E4, E2:E16, etc., not something like C4:E16.

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!!