VLOOKUP And Return Multiple Corresponding Values

[BORUCH]

HI

I have a table that looks as follows

[TABLE="width: 500"]
<tbody>[TR]
[TD]customer # and invoice number[/TD]
[TD]note 1[/TD]
[TD]note 2[/TD]
[/TR]
[TR]
[TD]AA~123[/TD]
[TD]SAMPLE 1[/TD]
[TD]SAMPLE 2[/TD]
[/TR]
[TR]
[TD]AA~456[/TD]
[TD]SAMPLE 3[/TD]
[TD]SAMPLE 4[/TD]
[/TR]
[TR]
[TD]AA TOTAL[/TD]
[TD]SAMPLE 5[/TD]
[TD]SAMPLE 6[/TD]
[/TR]
[TR]
[TD]BB~789[/TD]
[TD]SAMPLE 7[/TD]
[TD]SAMPLE 8[/TD]
[/TR]
[TR]
[TD]BB~111[/TD]
[TD]SAMPLE 9[/TD]
[TD]SAMPLE 10[/TD]
[/TR]
[TR]
[TD]BB TOTAL[/TD]
[TD]SAMPLE 11[/TD]
[TD]SAMPLE 12[/TD]
[/TR]
</tbody>[/TABLE]

my goal is to put in a customer # in cell F5 (AA OR BB )

And return underneath all his invoices, and then I can do a VLOOKUP On all the invoice numbers and return the corresponding note

How can i accomplish that

the invoice numbers wouldbe the numbers after the ~

Any help is greatly appreciated

thanks <o></o>

 

[Aladin Akyurek]

Re: How To VLOOKUP And Return Multiple Corresponding Values

HI

Thank you very much for the formula

i was wondering if perhaps you can convert this formula to a vba, so for ex.
I would enter a customer number in cell F1 and click a button and it would list all invoices underneath just like the formula does

Thanks

HI

Since my sheets have a lot of data an array formula like above is taking to long i was thinking that a vba is probably faster

Let's try the following formula: in F2 just enter and copy down:

=IFERROR(INDEX($A$2:$A$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-ROW(INDEX($A$2:$A$9,1,1))+1)/((1-ISNUMBER(SEARCH("TOTAL",$A$2:$A$9)))*(ISNUMBER(SEARCH("|"&$F$1,"|"&$A$2:$A$9)))),ROWS($F$2:F2))),"")

This is expected to run faster.

In G2 just enter, copy across, and down:

=IF($F2="","",INDEX($A$2:$C$9,MATCH(SUBSTITUTE($F2,"~","~~"),$A$2:$A$9,0),MATCH(G$1,$A$1:$C$1,0)))

 

[BORUCH]

Re: How To VLOOKUP And Return Multiple Corresponding Values

HI

it is still slow, in my computer, this formula is even slower.
I guess a vba button would probably be the most efficient

 

[Aladin Akyurek]

Re: How To VLOOKUP And Return Multiple Corresponding Values

HI

it is still slow, in my computer, this formula is even slower.

Really amazed to hear this!...

I guess a vba button would probably be the most efficient

Not sure about this, but I want another formula chance...

Book1
	A	B	C	D	E	F	G	H
1	customer # and invoice number	note 1	note 2	new cus # + inv		AA
2	AA123~~	SAMPLE 1	SAMPLE 2	AA123		2	note 1	note 2
3	AA123~845612~	SAMPLE 3	SAMPLE 4	AA123 845612		AA123	SAMPLE 1	SAMPLE 2
4	AA123 TOTAL	SAMPLE 5	SAMPLE 6	#		AA123 845612	SAMPLE 3	SAMPLE 4
5	BB123~~	SAMPLE 7	SAMPLE 8	BB123
6	BB123~845613~	SAMPLE 9	SAMPLE 10	BB123 845613
7	BB123~X45665~	SAMPLE 11	SAMPLE 12	BB123 X45665
8	BB123~h12541~	SAMPLE 13	SAMPLE 14	BB123 h12541
9	BB123 TOTAL	SAMPLE 15	SAMPLE 16	#
Sheet1 (4)

In D2 just enter and copy down:

=IF(ISNUMBER(SEARCH(" TOTAL",A2)),"#",TRIM(SUBSTITUTE(A2,"~"," ")))

In F2 enter:

=COUNTIFS(D2:D9,F1&"*")

In F3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($F$4:F4)>$F$2,"",INDEX($D$2:$D$9,SMALL(IF(LEFT($D$2:$D$9,2)=$F$1,ROW($D$2:$D$9)-ROW($D$2)+1),ROWS($F$4:F4))))

In G3 just enter, copy across, and down:

=IF($F3="","",INDEX($B$2:$C$9,MATCH($F3&"*",$D$2:$D$9,0),MATCH(G$2,$B$1:$C$1,0)))

Is this set up admissible?

 

[BORUCH]

Re: How To VLOOKUP And Return Multiple Corresponding Values

the above formula involves to many columns and it's to messy i would prefer a vba

 

[Aladin Akyurek]

Re: How To VLOOKUP And Return Multiple Corresponding Values

the above formula involves to many columns and it's to messy i would prefer a vba

It's called trading off memory against speed. Thanks for the feedback and good luck.