Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VLOOKUP with multiple first column matches | Excel Worksheet Functions | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions | |||
vlookup with multiple lines of same value | Excel Worksheet Functions | |||
vlookup over multiple worksheets | Excel Worksheet Functions | |||
Can vlookup return multiple matches in a single cell? | Excel Discussion (Misc queries) |