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)*(She et2!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