Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple results in Vlookup
I have a similar question to the one posted on 3/24/2006 by "donkey brain"
but I am not sure I understand the answer. I am using Vlookup for a project where the 1st worksheet in a workbook is a master spreadsheet with all available account numbers. I need to compile all the data from multiple worksheets into this master spreadsheet. For example, the 2nd worksheet has some of the account numbers, the ones that have trusts. It does not include all the account numbers so I just want to look at the second worksheet, if it has the account number, return the trust name. So I am using vlookup to search for the account number on wksht 2, and return a particular column back into the necessary location on the master worksheet. The issue is, that on wksht 2, sometimes the account number is listed twice because they have more than one trust set up. If I use the frequency function to count how many times the account number is in wksht 2, and if it is greater than 0, how do I force vlookup to look at the next row below the first listing of the account number to get the second trust information? This assumes it is sorted so the duplicate account numbers will be right below each other. Thank you for your time and help with this. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple results in Vlookup
You could use a MATCH function which returns the row in a range instead of
the value, then you would combine it with an INDEX function to select the proper column. Something like: =IF(FREQUENCY(...)1, INDEX(myRangeToSearch, MATCH(mySoughtValue, myColumnRangeToMatch, 0), myColumnNumber), VLOOKUP(...)) =INDEX(A8:B11,MATCH("123456",A8:A11,0),2) Stephane Quenson -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple results in Vlookup
I forgot to write that you should add +1 to get the second account number, so
the formula is: =INDEX(A8:B11,MATCH(4,A8:A11,0)+1,2) -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking up multiple results with VLOOKUP | Excel Worksheet Functions | |||
Vlookup, multiple results (in different cells)? | Excel Worksheet Functions | |||
adding multiple vlookup results | Excel Worksheet Functions | |||
how to return multiple results in vlookup? | Excel Worksheet Functions | |||
Add multiple vlookup results | Excel Worksheet Functions |