INDEX MATCH-Multiple results with multiple columns

[themick]

My data table will have several thousand rows so below is asnippet to illustrate the problem.


Column G, ‘LOOKUPS’, is what I need to search out to match in ColumnB. There will be multiple instances of this number (not always) but most of the time, yes.

When it matches that number, it needs to put the results of the entire row so that a new table is created with just these matches, Columns I through M in this table.

I have tried various ways but need help on the Index/Match formula (if that is the best way to do this).

(Column A, JD=John Doe, so John Doe1, John Doe2, etc.)

If there is a link already discussing this, any help pointing mein that direction would be appreciated, thank you.

1 ​

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

2​

DATA​

LOOKUPS

RESULTS​

3​

JD1

18-2899

1,575.00​

76.32​

1,651.32​

18-2929

JD1

18-2929

30.00​

-​

30.00​

4​

JD1

18-2929

30.00​

-​

30.00​

18-3006

JD2

18-2929

255.00​

30.74​

285.74​

5​

JD2

18-2929

255.00​

30.74​

285.74​

18-3100

JD3

18-2929

255.00​

-​

255.00​

6​

JD3

18-2929

255.00​

-​

255.00​

18-3124

7​

JD1

18-2963

15.00​

-​

15.00​

JD2

18-3006

825.00​

37.03​

862.03​

8​

JD2

18-3006

825.00​

37.03​

862.03​

JD3

18-3006

459.00​

-​

459.00​

9​

JD3

18-3006

459.00​

-​

459.00​

JD4

18-3006

1,140.00​

20.29​

1,160.29​

10​

JD4

18-3006

1,140.00​

20.29​

1,160.29​

JD5

18-3006

500.00​

25.00​

525.00​

11​

JD1

18-3076

270.00​

320.13​

590.13​

12​

JD1

18-3082

1,635.00​

17.10​

1,652.10​

JD3

18-3100

2,278.00​

9.24​

2,287.24​

13​

JD2

18-3089

1,260.00​

28.67​

1,288.67​

JD4

18-3100

1,815.00​

13.68​

1,828.68​

14​

JD3

18-3100

2,278.00​

9.24​

2,287.24​

15​

JD4

18-3100

1,815.00​

13.68​

1,828.68​

JD1

18-3124

1,224.00​

21.83​

1,245.83​

16​

JD1

18-3106

833.00​

-​

833.00​

JD2

18-3124

300.00​

4.27​

304.27​

17​

JD1

18-3124

1,224.00​

21.83​

1,245.83​

JD3

18-3124

-​

4.82​

4.82​

18​

JD2

18-3124

300.00​

4.27​

304.27​

19​

JD3

18-3124

-​

4.82​

4.82​

20​

JD5

18-3006

500.00​

25.00​

525.00​

<tbody>
</tbody>

 

[C Moore]

A pivot table might do what you want. I labeled your columns Name, Code, Net, Tax, Total (for example sake). Create a pivot table in exisiting sheet (e.g. O2) with Rows being Code and Name, sigma values being sum of net, sum of tax, and sum of total. Result was the following when filtering Code for the ones to lookup. (Looked really close to your results, with the ability to filter the code allowing for easy changes)

Row Labels	Sum of net	Sum of tax	Sum of total
18-2929	540	30.74	570.74
JD1	30	0	30
JD2	255	30.74	285.74
JD3	255	0	255
18-3006	2924	82.32	3006.32
JD2	825	37.03	862.03
JD3	459	0	459
JD4	1140	20.29	1160.29
JD5	500	25	525
18-3100	4093	22.92	4115.92
JD3	2278	9.24	2287.24
JD4	1815	13.68	1828.68
18-3124	1524	30.92	1554.92
JD1	1224	21.83	1245.83
JD2	300	4.27	304.27
JD3	0	4.82	4.82
Grand Total	9081	166.9	9247.9

<tbody>
</tbody>

 

[themick]

A pivot table might do what you want. I labeled your columns Name, Code, Net, Tax, Total (for example sake). Create a pivot table in exisiting sheet (e.g. O2) with Rows being Code and Name, sigma values being sum of net, sum of tax, and sum of total. Result was the following when filtering Code for the ones to lookup. (Looked really close to your results, with the ability to filter the code allowing for easy changes)

Row Labels	Sum of net	Sum of tax	Sum of total
18-2929	540	30.74	570.74
JD1	30		30
JD2	255	30.74	285.74
JD3	255		255
18-3006	2924	82.32	3006.32
JD2	825	37.03	862.03
JD3	459		459
JD4	1140	20.29	1160.29
JD5	500	25	525
18-3100	4093	22.92	4115.92
JD3	2278	9.24	2287.24
JD4	1815	13.68	1828.68
18-3124	1524	30.92	1554.92
JD1	1224	21.83	1245.83
JD2	300	4.27	304.27
JD3		4.82	4.82
Grand Total	9081	166.9	9247.9

<tbody>
</tbody>

Thank you for that. I am working from version 2010 Excel , how do I filter based on external range/list? Than you

 

[themick]

Thank you for that. I am working from version 2010 Excel , how do I filter based on external range/list? Than you

Followup - the key here for me is, in the real data, the list is probably 75,000 rows and the filtered list is a set of lookup codes (Col G) 600-750 large - what is the best way to filter this so only those 600-750 are displayed using Excel 2010?

 

[C Moore]

Wow, that's a lot of lookups! Without VBA, I don't know how you'd do it without manually clicking all those boxes.

 

[themick]

Wow, that's a lot of lookups! Without VBA, I don't know how you'd do it without manually clicking all those boxes.

For now, I did was create a column using this formula on sample data:

=IF(ISNA(VLOOKUP($A2,$H$2:$H$501,1,FALSE)), "FALSE", "TRUE")

With ColH being the lookup values. I then filter it based on 'TRUE', this does give me the filtered list but I am wondering if this is the best way to do this...
Thank you -