how to return a list of values based off one cell

ab9788

New Member
Joined
Nov 8, 2018
Messages
5
Office Version
  1. 365
Platform
  1. Windows
hey everyone,

here is the scenario:

i have a huge list of data with about 20 columns of different metrics. This data represents different customers and each row/customer has different data, however, there is one line of data that groups that customer with other similar customers.

on a separate tab, i would like to select the customer group from a drop down or just type it in and when i do, all of the customers that are in that group populate in the cells below it. For some customer groups there are 1-5 customers, for others, there are 100 or more.

can this be done???

thanks!!!!!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Yes, that's a common request. But the best way to do it depends on what version of Excel you have. I suggest you go to your member profile page and select your version, then we can give you an appropriate answer.
 
Upvote 0
hey Eric - done. I am on MSO 365 (16.0.13801.20772) 64 bit.
 
Upvote 0
Depending on how you want it to work you could create a named range and then use the indirect function which basically looks to match a cell with a named range and then give the values for that range
 
Upvote 0
As long as you have 365, it's dead easy:

Book1
ABCDEFGHIJKLMNO
1NameCodeMetric1Metric2Metric3Metric4CodeNameCodeMetric1Metric2Metric3Metric4
2A4V4O7F7S23G3G9R5R6U5
3B4E2G7G2R7H3C5Z2T5O7
4C1J3L2J1J3J3S7Y4B6A9
5D2O9O2X6G1K3E3E8I8R9
6E2N1F5L3H7
7F1S8Q9C3W3
8G3G9R5R6U5
9H3C5Z2T5O7
10I4I7J4D6O7
11J3S7Y4B6A9
12K3E3E8I8R9
13L2S3N7X2Z3
14M1I2W2Z3M9
15N1X5F5X3T4
16
Sheet13
Cell Formulas
RangeFormula
J2:O5J2=FILTER(A2:F15,B2:B15=H2,"No matches")
Dynamic array formulas.
 
Upvote 0
how do i set up the dynamic array?

on my sheet - columns A-U have data for all customers. I need to be able to type in 1 customer (that would match column R) into a cell on a separate tab and have everything that matches that value in column R populate below. I need to bring in cells A, U, R, S, O and P.
 
Upvote 0
Here's one way:

Book1
ABCDEFGHIJKLMNOPQRSTU
1NameColumn BColumn CColumn DColumn EColumn FColumn GColumn HColumn IColumn JColumn KColumn LColumn MColumn NColumn OColumn PColumn QCodeColumn SColumn TColumn U
2AB2C2D2E2F2G2H2I2J2K2L2M2N2O2P2Q24S2T2U2
3BB3C3D3E3F3G3H3I3J3K3L3M3N3O3P3Q34S3T3U3
4CB4C4D4E4F4G4H4I4J4K4L4M4N4O4P4Q41S4T4U4
5DB5C5D5E5F5G5H5I5J5K5L5M5N5O5P5Q52S5T5U5
6EB6C6D6E6F6G6H6I6J6K6L6M6N6O6P6Q62S6T6U6
7FB7C7D7E7F7G7H7I7J7K7L7M7N7O7P7Q71S7T7U7
8GB8C8D8E8F8G8H8I8J8K8L8M8N8O8P8Q83S8T8U8
9HB9C9D9E9F9G9H9I9J9K9L9M9N9O9P9Q93S9T9U9
10IB10C10D10E10F10G10H10I10J10K10L10M10N10O10P10Q104S10T10U10
11JB11C11D11E11F11G11H11I11J11K11L11M11N11O11P11Q113S11T11U11
12KB12C12D12E12F12G12H12I12J12K12L12M12N12O12P12Q123S12T12U12
13LB13C13D13E13F13G13H13I13J13K13L13M13N13O13P13Q132S13T13U13
14MB14C14D14E14F14G14H14I14J14K14L14M14N14O14P14Q141S14T14U14
15NB15C15D15E15F15G15H15I15J15K15L15M15N15O15P15Q151S15T15U15
16
17Match value3
18NameColumn UColumn RColumn SColumn OColumn P
19GU83S8O8P8
20HU93S9O9P9
21JU113S11O11P11
22KU123S12O12P12
Sheet13
Cell Formulas
RangeFormula
A19:F22A19=INDEX(A2:U15,FILTER(ROW(A2:A15)-ROW(A2)+1,R2:R15=B17),{1,21,18,19,15,16})
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
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