Multiple results in Vlookup

[Guest]

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.

 

[squenson via OfficeKB.com]

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

 

[squenson via OfficeKB.com]

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)