IF ISNA VLOOKUP problem

[Guest]

Hi all

I have a postcode file A1: H1656
Column A = postcod
Column B = subur
I also have a spreadsheet with either suburb names or postcodes. What I'm trying to do is 2 lookups (separately is ok)
1 Lookup the suburb and return the postcod
2 Lookup the postcode and return the subur

I will consolidate these later
The problem I have is that if I lookup a suburb to return a postcode and the lookup value is already a suburb I get an error: #N/A

I've tried:=IF(ISNA(VLOOKUP(C2,'Postcode file'!$A$2:$J$16563,2,FALSE),"",VLOOKUP(C2,'Postcode file'!$A$2:$J$16563,2,FALSE

But it's not working, "formula contains an error

What am I missin

Thank

Alb

 

[JulieD]

Hi Alby

couple of points

if column A is postcode & column B is suburb then how can
VLOOKUP(C2,'Postcode file'!$A$2:$J$16563,2,FALSE)
be returning the postcode anyway as column "2" relates to column B

secondly, AFAIK the VLOOKUP function requires that the lookup_value (1st
parameter) is the leftmost column of the table_array (2nd parameter), maybe
this is your problem?

when i've tested it looking up a value which is the result of a vlookup
works fine, e.g.

table_array
Column A Column B Column C
PostCode Suburb =A1

when i write the VLookup Statement i define two table areas depending on
whether i'm looking up the postcode to return the suburb(table_array =
A1:B100) or whether i'm looking up the suburb to return the postcode
(table_array = B1:C100)

Hope this helps
Cheers
JulieD

Hi all,

I have a postcode file A1: H16563
Column A = postcode
Column B = suburb
I also have a spreadsheet with either suburb names or postcodes. What I'm

trying to do is 2 lookups (separately is ok),

1 Lookup the suburb and return the postcode
2 Lookup the postcode and return the suburb

I will consolidate these later.
The problem I have is that if I lookup a suburb to return a postcode and

the lookup value is already a suburb I get an error: #N/A.

 

[CLR]

Looks to me like you need a couple of more ) as so,

=IF(ISNA(VLOOKUP(C2,'Postcode
file'!$A$2:$J$16563,2,FALSE)),"",VLOOKUP(C2,'Postcode
file'!$A$2:$J$16563,2,FALSE))

Vaya con Dios,
Chuck, CABGx3

Hi all,

I have a postcode file A1: H16563
Column A = postcode
Column B = suburb
I also have a spreadsheet with either suburb names or postcodes. What I'm

trying to do is 2 lookups (separately is ok),

1 Lookup the suburb and return the postcode
2 Lookup the postcode and return the suburb

I will consolidate these later.
The problem I have is that if I lookup a suburb to return a postcode and

the lookup value is already a suburb I get an error: #N/A.

 

[Guest]

thanks clr