Is it possible to have a VLOOKUP wit two answers (results)

[cubs610]

Hello all.. question. Can you do a vlookup where the answer can be 2 or more answers. Example : This is the information I have and would like to come up with the information on the bottom.


Factory Invoice Number | Invoice No


5462444 3399808

5462444 3399829

5462445 3398920

5462446 3398916

5462447 3398917

5462455 3399776

5462455 3399825

5462456 3398881

5462456 3398927


I want to know that Factory invoice number has two invoices.


Example using information above


5462444 = 3399808,3399829


5462455 = 3399776, 3399825


I'm thinking the solution may be a nested formula involving the MATCH, INDEX, and something else I can't put my finger on. Any thoughts?


Thanks,

Dave

 

[Sajan]

Hello Dave,

Assuming that you are OK with listing the Invoice #s in consecutive columns, instead of in a single cell, you could try the following formulas.


For ease of reference, I have assumed that the Factory Invoice data is named "FInv" and the other Invoice data is named "Inv".


For the data you posted, I got the following results, setup in cells F1:H7:

[pre]

UniqueFInv	Associated Inv >>>
5462444	        3399808		3399829
5462445		3398920		---
5462446		3398916		---
5462447		3398917		---
5462455		3399776		3399825
5462456		3398881		3398927

[/pre]
To create the UniqueFInv column, use the following formula (shown for cell F2):

=IFERROR(INDEX(FInv, MATCH(TRUE, ISNA(MATCH(FInv, $F$1:$F1,0)),0)), "---")

enter with Ctrl + Shift + Enter

Copy down to additional rows until you get "---"


To create the second and additional columns for the associated invoices, use the following formula (shown for cell G2):

=IFERROR(SMALL(IF(FInv=$F2, Inv), COLUMNS($G$2:G$2)), "---")

enter with Ctrl + Shift + Enter

Copy right to additional columns, and to additional rows, until you get "---"


(On the other hand, if you need to have the associated invoice numbers in a single cell, then you would need a VBA based solution.)


Cheers,

Sajan.

 

[cubs610]

Hey Sajan,


Thank you very much! I appreciate your help.


Best,

Dave