Index-Aggregate-Lookup Question

[nburaq]

Hi Gents,
I have two sheets and I would like to find and place the results from 2nd sheet to 1st sheet. I have tried xlookup function but this function does not work well with if there are multiple match results. I entered some values manually to show what I mean exactly by highlighting the cells. Here are my tables;

sample3.xlsx
	A	B	C	D
1	Item	Call	Group	Counter
2	100		18-CONSTRUC	3
3	110		29-CONSTRUC	6
4	120		67-CONSTRUC	8
5	130		77-CONSTRUC	3
6	140		78-CONSTRUC	3
7	150		81-CONSTRUC	3
8	160		83-CONSTRUC	3
9	170		84-CONSTRUC	3
10	220	9	85-CONSTRUC	3
11	220	10	85-CONSTRUC	3
12	250		85-CONSTRUC	3
13	260		91-CONSTRUC	12
14	270		94-CONSTRUC	12
15	280		97-CONSTRUC	3
16	290		99-CONSTRUC	3
17	300	11	85-CONSTRUC	3
Sheet1

sample3.xlsx
	A	B	C	D
1	Item	Call	Group	Counter
2	100	5	18-CONSTRUC	3
3	110	18	29-CONSTRUC	6
4	120	23	67-CONSTRUC	8
5	130	45	77-CONSTRUC	3
6	140	26	78-CONSTRUC	3
7	150	19	81-CONSTRUC	3
8	160	8	83-CONSTRUC	3
9	170	77	84-CONSTRUC	3
10	220	9	85-CONSTRUC	3
11	220	10	85-CONSTRUC	3
12	250	1	85-CONSTRUC	3
13	260	4	91-CONSTRUC	12
14	270	8	94-CONSTRUC	12
15	280	34	97-CONSTRUC	3
16	290	45	99-CONSTRUC	3
17	300	11	85-CONSTRUC	3
Sheet2

Thanks in advance!

 

[jasonb75]

That doesn't tell us a lot, you've shown 2 copies of the result table but not shown the source. Half a question gets you half an answer.

=AGGREGATE(15,6,result range / (criteria range = C2) ,COUNTIF(C$2:C2,C2))

 

[nburaq]

That doesn't tell us a lot, you've shown 2 copies of the result table but not shown the source. Half a question gets you half an answer.

=AGGREGATE(15,6,result range / (criteria range = C2) ,COUNTIF(C$2:C2,C2))

Yes to make things simple I made 2 copies of table. Sheet 2 includes everything to be used for sheet1. So you see 3 results in sheet1 entered manually to give an idea. (The end result of course look like a copy of sheet 2 since both tables are identical. I hope I would be helpful.

 

[jasonb75]

That makes no sense, if both are identical then you don't need a lookup of any kind, just enter =Sheet2!B2 into B2 on sheet 1 and fill down.

 

[nburaq]

Jason, think that in both sheets there are 1000 rows so should I copy all 1000 rows here? I just used a part of it. Also think that maybe a formula is gonna match automatically so that it will be peace for the eyes right? No need to match manually by one by. I understand your problem here is the not identical table for you

Book1.xlsx
	A	B	C	D
1	Item	Call	Group	Counter
2	140	26	78-CONSTRUC	3
3	150	19	81-CONSTRUC	3
4	120	23	67-CONSTRUC	8
5	130	45	77-CONSTRUC	3
6	100	5	18-CONSTRUC	3
7	110	18	29-CONSTRUC	6
8	160	8	83-CONSTRUC	3
9	170	77	84-CONSTRUC	3
10	220	9	85-CONSTRUC	3
11	220	10	85-CONSTRUC	3
12	250	1	85-CONSTRUC	3
13	260	4	91-CONSTRUC	12
14	290	45	99-CONSTRUC	3
15	300	11	85-CONSTRUC	3
16	270	8	94-CONSTRUC	12
17	280	34	97-CONSTRUC	3
Sheet2

 

[jasonb75]

Here's a good tip for you

The end result of course look like a copy of sheet 2 since both tables are identical.

Don't say it's identical if it's not identical, we can not read your mind, only your posts. Identical means exactly the same, the exact same data sorted in the exact same order. This is what you have shown us in the first post and clarified as correct in post 3, now it appears that this is not correct. If you tell us they are identical then you will get an answer based on that. If that answer doesn't give you what you want then it is not the answer that is wrong, it is your question.

I've given up trying to make sense of what you're telling me and gone with what I think you actually want, formula is set for 2000 rows of data in sheet 2. Enter it into B2 on sheet 1 and fill down.

=AGGREGATE(15,6,Sheet2!$B$2:$B$2000/(Sheet2!$C$2:$C$2000=C2),COUNTIF(C$2:C2,C2))

 

[nburaq]

Here's a good tip for you

Don't say it's identical if it's not identical, we can not read your mind, only your posts. Identical means exactly the same, the exact same data sorted in the exact same order. This is what you have shown us in the first post and clarified as correct in post 3, now it appears that this is not correct. If you tell us they are identical then you will get an answer based on that. If that answer doesn't give you what you want then it is not the answer that is wrong, it is your question.

I've given up trying to make sense of what you're telling me and gone with what I think you actually want, formula is set for 2000 rows of data in sheet 2. Enter it into B2 on sheet 1 and fill down.

=AGGREGATE(15,6,Sheet2!$B$2:$B$2000/(Sheet2!$C$2:$C$2000=C2),COUNTIF(C$2:C2,C2))

It works perfectly! Thanks again for your help and concern and sorry for the misunderstanding.