Vlookup when more than one possiblity

[samenvoegen van sheets]

Hello,

I'm using the VLOOKUP function to find some place's code within a table.
My problem is that some places in the world are writen the same way but are
not in the same contry and so don't have the same code.
now if i'm using the Vlookup it will only take the first name and sometimes
the wrong code.
How can i do, to let the function make an error if there are two or more
similair places?


thks in advance,
Flo

[Dav]

If your name that you are looking up is in a2

countif(Range of names,a2) will count the number of occurences

so if the range of names is in a4:a100

if(countif(a4:a100,a2)>1,"Error",vlookup(whatever your formula is))

Would work

Regards

Dav

[Kevin Vaughn]

Not sure if I understand what you are asking, and you apparently already have
an answer for your question (returning an error,) but if you want to return a
value based on more than one column, an array formula (entered with
cntl-shift-enter, not just enter) might do the trick.
For instance, given the following data in A1:C4

Paris France 10150
San Diego CA 92010
Paris Florida abcde
San Diego Moon help me

And the following data in A7 & B7

Paris Florida

Then this formula (array entered) will return abcde:

=INDEX($C$1:$C$4,MATCH(A7&B7,$A$1:$A$4&$B$1:$B$4,0))

Note, there is no error checking so a non match will return #N/A. If this
bothers you, you could modify it to be:

=IF(ISNA(INDEX($C$1:$C$4,MATCH(A7&B7,$A$1:$A$4&$B$1:$B$4,0))),"",INDEX($C$1:$C$4,MATCH(A7&B7,$A$1:$A$4&$B$1:$B$4,0)))

--
Kevin Vaughn


"samenvoegen van sheets" wrote:

> Hello,
>
> I'm using the VLOOKUP function to find some place's code within a table.
> My problem is that some places in the world are writen the same way but are
> not in the same contry and so don't have the same code.
> now if i'm using the Vlookup it will only take the first name and sometimes
> the wrong code.
> How can i do, to let the function make an error if there are two or more
> similair places?
>
>
> thks in advance,
> Flo
>