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>

 

[James006]

Re: How To VLOOKUP And Return Multiple Corresponding Values

Hi,

There are several possible solutions ...:wink:

The quick one is to use from the Menu Advanced Filter ...

HTH

 

[BORUCH]

Re: How To VLOOKUP And Return Multiple Corresponding Values

Hi,

There are several possible solutions ...:wink:

The quick one is to use from the Menu Advanced Filter ...

HTH

I'm looking for a formula

 

[BORUCH]

Re: How To VLOOKUP And Return Multiple Corresponding Values

Any updates

 

[Aladin Akyurek]

Re: How To VLOOKUP And Return Multiple Corresponding Values

Book1
	A	B	C	D	E	F	G	H
1	customer # and invoice number	note 1	note 2			AA	note 1	note 2
2	AA~123	SAMPLE 1	SAMPLE 2			AA~123	SAMPLE 1	SAMPLE 2
3	AA~456	SAMPLE 3	SAMPLE 4			AA~456	SAMPLE 3	SAMPLE 4
4	AA TOTAL	SAMPLE 5	SAMPLE 6
5	BB~789	SAMPLE 7	SAMPLE 8
6	BB~111	SAMPLE 9	SAMPLE 10
7	BB TOTAL	SAMPLE 11	SAMPLE 12
Sheet1

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

=IFERROR(INDEX($A$2:$A$7,SMALL(IF(ISNUMBER(RIGHT($A$2:$A$7)+0),IF(ISNUMBER(SEARCH("|"&$F$1,"|"&$A$2:$A$7)),ROW($A$2:$A$7)-ROW(INDEX($A$2:$A$7,1,1))+1)),ROWS($F$2:F2))),"")

In G2 just enter, copy across, and down:

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

 

[BORUCH]

Re: How To VLOOKUP And Return Multiple Corresponding Values

hi

i just realized a little issue with my data set i can have the column as follows

[TABLE="width: 500"]
<tbody>[TR]
[TD]AA123~~
[/TD]
[/TR]
[TR]
[TD]AA123~845612~[/TD]
[/TR]
[TR]
[TD]AA123 TOTAL[/TD]
[/TR]
[TR]
[TD]BB123~~[/TD]
[/TR]
[TR]
[TD]BB123~845613~[/TD]
[/TR]
[TR]
[TD]BB123~X45665~[/TD]
[/TR]
[TR]
[TD]BB123~h12541~[/TD]
[/TR]
[TR]
[TD]BB123 TOTAL[/TD]
[/TR]
[TR]
[TD]ETC....[/TD]
[/TR]
</tbody>[/TABLE]

How can i accommodate all of these varietals ?

 

[Aladin Akyurek]

Re: How To VLOOKUP And Return Multiple Corresponding Values

hi

i just realized a little issue with my data set i can have the column as follows

[TABLE="width: 500"]
<tbody>[TR]
[TD]AA123~~
[/TD]
[/TR]
[TR]
[TD]AA123~845612~[/TD]
[/TR]
[TR]
[TD]AA123 TOTAL[/TD]
[/TR]
[TR]
[TD]BB123~~[/TD]
[/TR]
[TR]
[TD]BB123~845613~[/TD]
[/TR]
[TR]
[TD]BB123~X45665~[/TD]
[/TR]
[TR]
[TD]BB123~h12541~[/TD]
[/TR]
[TR]
[TD]BB123 TOTAL[/TD]
[/TR]
[TR]
[TD]ETC....[/TD]
[/TR]
</tbody>[/TABLE]

How can i accommodate all of these varietals ?

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

=IFERROR(INDEX($A$2:$A$9,SMALL(IF(ISNUMBER(SEARCH("|"&$F$1,"|"&$A$2:$A$9)),ROW($A$2:$A$9)-ROW(INDEX($A$2:$A$9,1,1))+1),ROWS($F$2:F2))),"")

This lists the items with TOTAL also. The following formula instead of the foregoing excludes such items.

=IFERROR(INDEX($A$2:$A$9,SMALL(IF(1-ISNUMBER(SEARCH("TOTAL",$A$2:$A$9)),IF(ISNUMBER(SEARCH("|"&$F$1,"|"&$A$2:$A$9)),ROW($A$2:$A$9)-ROW(INDEX($A$2:$A$9,1,1))+1)),ROWS($F$2:F2))),"")

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

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

=IFERROR(INDEX($A$2:$A$9,SMALL(IF(ISNUMBER(SEARCH("|"&$F$1,"|"&$A$2:$A$9)),ROW($A$2:$A$9)-ROW(INDEX($A$2:$A$9,1,1))+1),ROWS($F$2:F2))),"")

This lists the items with TOTAL also. The following formula instead of the foregoing excludes such items.

=IFERROR(INDEX($A$2:$A$9,SMALL(IF(1-ISNUMBER(SEARCH("TOTAL",$A$2:$A$9)),IF(ISNUMBER(SEARCH("|"&$F$1,"|"&$A$2:$A$9)),ROW($A$2:$A$9)-ROW(INDEX($A$2:$A$9,1,1))+1)),ROWS($F$2:F2))),"")

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)))

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

 

[James006]

Re: How To VLOOKUP And Return Multiple Corresponding Values

Hi,

Looking for a Formula ... or for a Macro...???

Seems you are back to the Filter solution ...:wink:

 

[BORUCH]

Re: How To VLOOKUP And Return Multiple Corresponding Values

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