VLOOKUP And Return Multiple Corresponding Values

BORUCH

Well-known Member
Joined
Mar 1, 2016
Messages
528
Office Version
  1. 365
Platform
  1. Windows
HI

I have a table that looks as follows

customer # and invoice numbernote 1note 2
AA~123SAMPLE 1SAMPLE 2
AA~456SAMPLE 3SAMPLE 4
AA TOTALSAMPLE 5SAMPLE 6
BB~789SAMPLE 7SAMPLE 8
BB~111SAMPLE 9SAMPLE 10
BB TOTALSAMPLE 11SAMPLE 12

<tbody>
</tbody>

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:p></o:p>

 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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
 
Upvote 0
Re: How To VLOOKUP And Return Multiple Corresponding Values

Any updates
 
Upvote 0
Re: How To VLOOKUP And Return Multiple Corresponding Values


Book1
ABCDEFGH
1customer # and invoice numbernote 1note 2AAnote 1note 2
2AA~123SAMPLE 1SAMPLE 2AA~123SAMPLE 1SAMPLE 2
3AA~456SAMPLE 3SAMPLE 4AA~456SAMPLE 3SAMPLE 4
4AA TOTALSAMPLE 5SAMPLE 6
5BB~789SAMPLE 7SAMPLE 8
6BB~111SAMPLE 9SAMPLE 10
7BB TOTALSAMPLE 11SAMPLE 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)))
 
Upvote 0
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

AA123~~
AA123~845612~
AA123 TOTAL
BB123~~
BB123~845613~
BB123~X45665~
BB123~h12541~
BB123 TOTAL
ETC....

<tbody>
</tbody>

How can i accommodate all of these varietals ?
 
Upvote 0
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

AA123~~
AA123~845612~
AA123 TOTAL
BB123~~
BB123~845613~
BB123~X45665~
BB123~h12541~
BB123 TOTAL
ETC....

<tbody>
</tbody>

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)))
 
Upvote 0
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
 
Upvote 0
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:
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top