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
|