i'm actually trying to make a postcode search. Entering the postcode, it
comes up with the suburb name fine.
But when i know the suburb name, but want to find the postcode, the only
thing i get is #N/A ... Here is the string (used for both)

COLUMN A is the postcodes
COLUMN B is the suburbs
*The one that works* =IF(B10="","",VLOOKUP(B10,data!$A$3:$E$1002,2))
*The one that doesnt* =IF(B5<"",VLOOKUP(B5,data!$A$3:$E$1002,1))

roza_j2002 wrote:
i'm actually trying to make a postcode search. Entering the postcode,
it comes up with the suburb name fine.
But when i know the suburb name, but want to find the postcode, the
only thing i get is #N/A ... Here is the string (used for both)

COLUMN A is the postcodes
COLUMN B is the suburbs
*The one that works*
=IF(B10="","",VLOOKUP(B10,data!$A$3:$E$1002,2))
*The one that doesnt* =IF(B5<"",VLOOKUP(B5,data!$A$3:$E$1002,1))


If you check the on line help for VLOOKUP, you'll see that the column in
which you are searching the lookup value *must* be the first in the table,
i.e. you can search from left to right, but you cannot search from right to
left, with VLOOKUP. To search from right to left you could use a trick, i.e.
insert a column before the first and insert a formula similar to this:

=C3

and copy down across the rows

Another way is to use OFFSET + MATCH:


=IF(B5<"",OFFSET(data!$B$3,MATCH(B5,data!$B$3:$B$ 1002,0)-1,-1,1,1)


to see references about OFFSET + MATCH you can see at Chip Pearson's site:

http://www.cpearson.com/excel/lookups.htm#LeftLookup

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Adding a cell before did not fix it. But it is coming up with random numbers
OR the last number in the column.

"Franz Verga" wrote:

roza_j2002 wrote:
i'm actually trying to make a postcode search. Entering the postcode,
it comes up with the suburb name fine.
But when i know the suburb name, but want to find the postcode, the
only thing i get is #N/A ... Here is the string (used for both)

COLUMN A is the postcodes
COLUMN B is the suburbs
*The one that works*
=IF(B10="","",VLOOKUP(B10,data!$A$3:$E$1002,2))
*The one that doesnt* =IF(B5<"",VLOOKUP(B5,data!$A$3:$E$1002,1))


If you check the on line help for VLOOKUP, you'll see that the column in
which you are searching the lookup value *must* be the first in the table,
i.e. you can search from left to right, but you cannot search from right to
left, with VLOOKUP. To search from right to left you could use a trick, i.e.
insert a column before the first and insert a formula similar to this:

=C3

and copy down across the rows

Another way is to use OFFSET + MATCH:


=IF(B5<"",OFFSET(data!$B$3,MATCH(B5,data!$B$3:$B$ 1002,0)-1,-1,1,1)


to see references about OFFSET + MATCH you can see at Chip Pearson's site:

http://www.cpearson.com/excel/lookups.htm#LeftLookup

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

OFFSET & MATCH also did not work.

"Franz Verga" wrote:

roza_j2002 wrote:
i'm actually trying to make a postcode search. Entering the postcode,
it comes up with the suburb name fine.
But when i know the suburb name, but want to find the postcode, the
only thing i get is #N/A ... Here is the string (used for both)

COLUMN A is the postcodes
COLUMN B is the suburbs
*The one that works*
=IF(B10="","",VLOOKUP(B10,data!$A$3:$E$1002,2))
*The one that doesnt* =IF(B5<"",VLOOKUP(B5,data!$A$3:$E$1002,1))


If you check the on line help for VLOOKUP, you'll see that the column in
which you are searching the lookup value *must* be the first in the table,
i.e. you can search from left to right, but you cannot search from right to
left, with VLOOKUP. To search from right to left you could use a trick, i.e.
insert a column before the first and insert a formula similar to this:

=C3

and copy down across the rows

Another way is to use OFFSET + MATCH:


=IF(B5<"",OFFSET(data!$B$3,MATCH(B5,data!$B$3:$B$ 1002,0)-1,-1,1,1)


to see references about OFFSET + MATCH you can see at Chip Pearson's site:

http://www.cpearson.com/excel/lookups.htm#LeftLookup

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

If you add an extra column in C then in C3 (or whichever row has the
first postcode in) use the formula =A3 and copy this down the column as
far as needed.

Then you can use your formula with a slight change
=IF(B5<"",VLOOKUP(B5,data!$b$3:$f$1002,2))

You will also need to change your other formula slightly to allow for
the added column
=IF(B10="","",VLOOKUP(B10,data!$A$3:$f$1002,2))

Hope this helps

Regards

Carl
roza_j2002 Wrote:
i'm actually trying to make a postcode search. Entering the postcode,
it
comes up with the suburb name fine.
But when i know the suburb name, but want to find the postcode, the
only
thing i get is #N/A ... Here is the string (used for both)

COLUMN A is the postcodes
COLUMN B is the suburbs
*The one that works*
=IF(B10="","",VLOOKUP(B10,data!$A$3:$E$1002,2))
*The one that doesnt* =IF(B5<"",VLOOKUP(B5,data!$A$3:$E$1002,1))


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=570584

thanks for your help. but it's still only coming up with the last postcode.

"mr_teacher" wrote:


If you add an extra column in C then in C3 (or whichever row has the
first postcode in) use the formula =A3 and copy this down the column as
far as needed.

Then you can use your formula with a slight change
=IF(B5<"",VLOOKUP(B5,data!$b$3:$f$1002,2))

You will also need to change your other formula slightly to allow for
the added column
=IF(B10="","",VLOOKUP(B10,data!$A$3:$f$1002,2))

Hope this helps

Regards

Carl
roza_j2002 Wrote:
i'm actually trying to make a postcode search. Entering the postcode,
it
comes up with the suburb name fine.
But when i know the suburb name, but want to find the postcode, the
only
thing i get is #N/A ... Here is the string (used for both)

COLUMN A is the postcodes
COLUMN B is the suburbs
*The one that works*
=IF(B10="","",VLOOKUP(B10,data!$A$3:$E$1002,2))
*The one that doesnt* =IF(B5<"",VLOOKUP(B5,data!$A$3:$E$1002,1))


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=570584

if anyone wants it, i can email the file to you.

"roza_j2002" wrote:

i'm actually trying to make a postcode search. Entering the postcode, it
comes up with the suburb name fine.
But when i know the suburb name, but want to find the postcode, the only
thing i get is #N/A ... Here is the string (used for both)

COLUMN A is the postcodes
COLUMN B is the suburbs
*The one that works* =IF(B10="","",VLOOKUP(B10,data!$A$3:$E$1002,2))
*The one that doesnt* =IF(B5<"",VLOOKUP(B5,data!$A$3:$E$1002,1))

roza_j2002 wrote:
if anyone wants it, i can email the file to you.

If you could upload it to www.savefile.com I think should be better...


"roza_j2002" wrote:

i'm actually trying to make a postcode search. Entering the
postcode, it comes up with the suburb name fine.
But when i know the suburb name, but want to find the postcode, the
only thing i get is #N/A ... Here is the string (used for both)

COLUMN A is the postcodes
COLUMN B is the suburbs
*The one that works*
=IF(B10="","",VLOOKUP(B10,data!$A$3:$E$1002,2))
*The one that doesnt* =IF(B5<"",VLOOKUP(B5,data!$A$3:$E$1002,1))

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Does it help if you put the last parameter in your lookup formula - i.e.
an extra ",0" without the quotation marks immediately before the final
parentheses "))"? This should force vlookup to find only exact matches.

If you select the cell containing the formula and then click the "="
sign to the left of the formula bar above the spreadsheet, you should
get a dialogue box showing what you have to - or are able to - specify.
If you then click within the word VLOOKUP in the formula bar, you should
see the box pertaining to that function with its four parameter fields.
Putting a "1" or a "0" in the last box may give you different results,
depending on how the lookup data are organized.

Noel


roza_j2002 wrote

thanks for your help. but it's still only coming up with the last
postcode.

"mr_teacher" wrote:


If you add an extra column in C then in C3 (or whichever row has the
first postcode in) use the formula =A3 and copy this down the column
as
far as needed.

Then you can use your formula with a slight change
=IF(B5<"",VLOOKUP(B5,data!$b$3:$f$1002,2))

You will also need to change your other formula slightly to allow for
the added column
=IF(B10="","",VLOOKUP(B10,data!$A$3:$f$1002,2))

Hope this helps

Regards

Carl
roza_j2002 Wrote:
i'm actually trying to make a postcode search. Entering the
postcode,
it
comes up with the suburb name fine.
But when i know the suburb name, but want to find the postcode, the
only
thing i get is #N/A ... Here is the string (used for both)

COLUMN A is the postcodes
COLUMN B is the suburbs
*The one that works*
=IF(B10="","",VLOOKUP(B10,data!$A$3:$E$1002,2))
*The one that doesnt* =IF(B5<"",VLOOKUP(B5,data!$A$3:$E$1002,1))


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile:
http://www.excelforum.com/member.php...o&userid=34352
View this thread:
http://www.excelforum.com/showthread...hreadid=570584