V-LOOKUPS with multiple results

[MN_Excel]

Good morning,

I'm attempting to work with a v-lookup that will have multipe results. In column A of my spreadsheet are invoices numbers and in column B are account numbers. An invoice can have multiple accounts. For example, there are two lines for invoice "ABC", each with a different result in column B (see below)

Invoice Account
ABC Cash
ABC Receivable

My goal is to have the vlookup bring in both values, but in separate rows (see below)

Column A Column B Column C
ABC Cash Receivable

Is this possible? Thanks in advance for any help.

 

[Andrew Poulsom]

Are the two rows always one below the other?

 

[MN_Excel]

Unfortunately not

 

[Andrew Poulsom]

Try these formulas:

=VLOOKUP("ABC",A:B,2,FALSE)
=VLOOKUP("ABC",INDEX(A:A,MATCH("ABC",A:A,FALSE)+1):B1000,2,FALSE)

 

[MN_Excel]

ADVERTISEMENT

I was working with the second formula you provided but had no luck. I've simplified the situation below. The source data is teh invoice and account columns. I've included both my expected results and formula results. Does this help troubleshoot

Invoice</SPAN>	Account</SPAN>
ABC</SPAN>	Cash</SPAN>		Formula Result</SPAN>
321</SPAN>	Asset</SPAN>			Act1</SPAN>	Act 2</SPAN>	Act 3</SPAN>
CBA</SPAN>	Receivable</SPAN>	ABC</SPAN>	Liability</SPAN>	Liability</SPAN>
123</SPAN>	Receivable</SPAN>	123</SPAN>
ABC</SPAN>	Liability</SPAN>
321</SPAN>	Cash</SPAN>
CBA</SPAN>	Equity</SPAN>
123</SPAN>	Master</SPAN>
ABC</SPAN>	Master</SPAN>
			Expected Result</SPAN>
				Act 1</SPAN>	Act 2</SPAN>	Act 3</SPAN>
			ABC</SPAN>	Cash</SPAN>	Liability</SPAN>	Master</SPAN>
			123</SPAN>	Receivable</SPAN>	Master</SPAN>

<TBODY>
</TBODY>

 

[Andrew Poulsom]

The first formula returns the first result and the second formula returns the second result. If there aremore than 2 entries you will need to use a different formula, as in:

http://www.mrexcel.com/forum/excel-questions/446147-vlookup-multiple-entries-reference.html

 

[MN_Excel]

Thanks for the help, I think I"m almost there! The link you provided doesn't get me exactly where I need to be in regards to cases where there are more than 2 entries, but I'm thinking the formula you provided earlier might-(VLOOKUP("ABC",INDEX(A:A,MATCH("ABC",A:A,FALSE)+1):B1000,2,FALSE). What is the +1 in the formula doing? I'm wondering if adjusting that would help me bring in the 3rd, 4th, 5th, etc results.

 

[Andrew Poulsom]

No you can't adjust the +1 to return subsequent matches. It's used to return a reference to the cell below the one containing the first match. Use the formulas in Posts 5 or 6 in the link I gave you.