how do i use VLOOKUP to find text and return a number?

[Guest]

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

 

[Franz Verga]

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

 

[Guest]

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

 

[Guest]

OFFSET & MATCH also did not work.

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

 

[mr_teacher]

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

 

[Guest]

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

 

[Guest]

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

 

[Franz Verga]

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

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

 

[Ildhund]

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