how to deal with multiple matches on vlookup?

doing a vlookup on company name - it may appear numerous times in the
spreadsheet I am looking up from, but I can't tell how it determines which
value to return. For example, Company ABC listed in 4 rows, 3 of which have
"no" and 1 has "yes". I want the yes. I have sorted alphabetically,
thinking it returns the first one. Need advice. Thanks.....Ash

I thought it does return the first one if you put the parameter "False" as
the fourth parameter.

I'm sure someone has a cleverer formula, but you could sort by company, then
descending by the yes/no column?

Daniel

"Ash" wrote:

> doing a vlookup on company name - it may appear numerous times in the
> spreadsheet I am looking up from, but I can't tell how it determines which
> value to return. For example, Company ABC listed in 4 rows, 3 of which have
> "no" and 1 has "yes". I want the yes. I have sorted alphabetically,
> thinking it returns the first one. Need advice. Thanks.....Ash

I do not quite understand what you want to achieve here. However, Vlookup
works best with a sorted database type list, where each name appears once
only. You will experience problems with multiple entries

"Daniel Bonallack" wrote:

> I thought it does return the first one if you put the parameter "False" as
> the fourth parameter.
>
> I'm sure someone has a cleverer formula, but you could sort by company, then
> descending by the yes/no column?
>
> Daniel
>
> "Ash" wrote:
>
> > doing a vlookup on company name - it may appear numerous times in the
> > spreadsheet I am looking up from, but I can't tell how it determines which
> > value to return. For example, Company ABC listed in 4 rows, 3 of which have
> > "no" and 1 has "yes". I want the yes. I have sorted alphabetically,
> > thinking it returns the first one. Need advice. Thanks.....Ash

Say you have the company name in A1 of sheet1

And sheet2 is laid out like this:
column A--list of company names
column B--Yes/no
column C--value to be brought back.

=INDEX(Sheet2!C:C,MATCH(1,(Sheet2!A1:A100=A1)*(Sheet2!B1:B100="yes"),0))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range (I used 100 rows) to match your data--but don't use the whole
column.

Ash wrote:
>
> doing a vlookup on company name - it may appear numerous times in the
> spreadsheet I am looking up from, but I can't tell how it determines which
> value to return. For example, Company ABC listed in 4 rows, 3 of which have
> "no" and 1 has "yes". I want the yes. I have sorted alphabetically,
> thinking it returns the first one. Need advice. Thanks.....Ash

--

Dave Peterson