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