Index and Match

[josvill2010]

HI Everybody,
There is a table that contains all information that I need to build another table.
At the moment I am searching manually and bringing the value from the other table across.
I was hoping to automate but the problem is that the search element has different values underneath in a different row and then in the next table.
I would like to bring this values across but I cannot remember how to ask the formula to bring the values below the Matching cell, and then from two columns to the right.
Attached is the example

TestingExcelFormula.xlsx
	A	B	C	D	E	F
1	PO #	Requestor	Supplier			2001-GL1
2	0	0	0
3	0	0	0
4
5
6	0	0	0			2001-GL2
Formula

Cell Formulas
Range	Formula
A2	A2	=INDEX(test!D:D,MATCH(F1,test!C:C,0))
B2	B2	=INDEX(test!F:F,MATCH(F1,test!C:C,0))
C2	C2	=INDEX(test!E:E,MATCH(F1,test!C:C,0))
A3	A3	=INDEX(test!D:D,MATCH(F1,test!C:C,0))
B3	B3	=INDEX(test!F:F,MATCH(F1,test!C:C,0))
C3	C3	=INDEX(test!E:E,MATCH(F1,test!C:C,0))
A6	A6	=INDEX(test!D:D,MATCH(F6,test!C:C,0))
B6	B6	=INDEX(test!F:F,MATCH(F6,test!C:C,0))
C6	C6	=INDEX(test!E:E,MATCH(F6,test!C:C,0))

TestingExcelFormula.xlsx
	A	B	C	D	E	F	G
1
2	L1	Test1	2001-GL1
3			GL	PO#	Supplier	Requester	PO Header Description
4			2001-GL1	100001	Company1	Joe	PO Great
5			2001-GL1	100002	Company2	Michael	PO Great 2
6			2001-GL1	100003	Company3	David	PO Great 3
7
8	L1	Test1	2001-GL2
9			GL		Supplier	Requester	PO Header Description
10			2001-GL2	100004	Company4	Richard	PO Great 4
11
test

 

[jasonb75]

Based on the example provided, try

Book1
	A	B	C	D	E	F	G
2	L1	Test1	2001-GL1
3			GL	PO#	Supplier	Requester	PO Header Description
4			2001-GL1	100001	Company1	Joe	PO Great
5			2001-GL1	100002	Company2	Michael	PO Great 2
6			2001-GL1	100003	Company3	David	PO Great 3
7
8	L1	Test1	2001-GL2
9			GL		Supplier	Requester	PO Header Description
10			2001-GL2	100004	Company4	Richard	PO Great 4
11
12		100001	Joe	Company1	2001-GL1
13		100002	Michael	Company2	2001-GL1
14		100003	David	Company3	2001-GL1
15		100004	Richard	Company4	2001-GL2
Sheet1

Cell Formulas
Range	Formula
B12:E15	B12	=INDEX(FILTER(C2:F10,(C2:C10<>"GL")*(D2:D10<>""),""),SEQUENCE(COUNTIFS(C2:C10,"<>GL",D2:D10,"<>")),{2,4,3,1})
Dynamic array formulas.

 

[josvill2010]

Based on the example provided, try

Book1
	A	B	C	D	E	F	G
2	L1	Test1	2001-GL1
3			GL	PO#	Supplier	Requester	PO Header Description
4			2001-GL1	100001	Company1	Joe	PO Great
5			2001-GL1	100002	Company2	Michael	PO Great 2
6			2001-GL1	100003	Company3	David	PO Great 3
7
8	L1	Test1	2001-GL2
9			GL		Supplier	Requester	PO Header Description
10			2001-GL2	100004	Company4	Richard	PO Great 4
11
12		100001	Joe	Company1	2001-GL1
13		100002	Michael	Company2	2001-GL1
14		100003	David	Company3	2001-GL1
15		100004	Richard	Company4	2001-GL2
Sheet1

Cell Formulas
Range	Formula
B12:E15	B12	=INDEX(FILTER(C2:F10,(C2:C10<>"GL")*(D2:D10<>""),""),SEQUENCE(COUNTIFS(C2:C10,"<>GL",D2:D10,"<>")),{2,4,3,1})
Dynamic array formulas.

Thanks for the help. I think this a couple of level ups from me.
Can you help me to understand what the formula does?

I see the filter C2:F10 is the sample.
C2:C10<>"GL" and x (D2:D10<>""). First part is find whatever doesn't have GL in Colum C2 to C10 and then multiply by whatever you find in D2 to D10 that is not zero.
,"") not sure what that does.

Sequence(Countifs(C2:C10,"<>GL",D2:D10,"<>")). Count the what is not GL in Column C and what it has a number in column D.
The last part is to bring the order of what the sample has.

I would like to bring the table but limit to the GL in column F. So I can create a sum for all the PO that I bring under each GL.
Can I link the C2:C10<>"GL" and x (D2:D10<>"") to F1 eg C2:C10=f1

 

[josvill2010]

This is the error I am getting in cell A2 . It can read the lines but it doesn't bring any of the information like Supplier, requestor.
It seems the number of rows that brings is correct.
Any suggestions? is it a problem with Sequence, the filter, or the Index?

TestingExcelFormula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Supplier	Requestor	Amount	Remaining			=INDEX(test!F:F,MATCH(F1,test!C:C,0))		L1	Test1	1	2	3	4	5	6	7			Amount	Remaining
2	#N/A	#N/A	#N/A	#N/A		2001-CONSUMABLES
3	#N/A	#N/A	#N/A	#N/A
4	#N/A	#N/A	#N/A	#N/A
5											2001-CONSUMABLES
6	#N/A	#N/A	#N/A	#N/A		2002-TOOLS							Supplier	Requester	PO Header Description	Create Date	Need by Date	Last Activity Date	PO Line Order Amt USD	Amt Billed USD
7									L1	Test1	2001-CONSUMABLES	2020214	Supplier	Requester					100000	75000
8											2001-CONSUMABLES	70000027165	Supplier	Requester					90000	65000
9											2001-CONSUMABLES	70000049091	Supplier	Requester					80000	55000
10
11	#N/A	#N/A	#N/A	#N/A		2003-ELEC
12													Supplier	Requester	PO Header Description	Create Date	Need by Date	Last Activity Date	PO Line Order Amt USD	Amt Billed USD
13											2002-TOOLS	2013281	Supplier	Requester					70000	45000
Formula PO (3)

Cell Formulas
Range	Formula
A2:D4,A6:D6	A2	=INDEX(FILTER($K:$U,($K:$K=$F2)*($L:$L<>""),""),SEQUENCE(COUNTIFS($K:$K,$F2,L:L,"<>")),{3,6,8,9})
S8:T9	S8	=S7-10000
A11:D11	A11	=INDEX(FILTER($K:$P,($K:$K=$F11)*($L:$L<>""),""),SEQUENCE(COUNTIFS($K:$K,$F11,L:L,"<>")),{2,4,3,1})
S13:T13	S13	=S9-10000
Dynamic array formulas.

 

[jasonb75]

Not sure why you're getting N/A's, this is what I get for the first 2 criteria. The third one returns an error because there is no matching data in the sample.
Note that I had to change the numbers at the end of the formula to match the data source but you should still be seeing a mixture of results and 0 values, not errors.

Book1
	A	B	C	D	E	F
1	Supplier	Requestor	Amount	Remaining
2	Supplier	Requester	75000	100000		2001-CONSUMABLES
3	Supplier	Requester	65000	90000
4	Supplier	Requester	55000	80000
5
6	Supplier	Requester	45000	70000		2002-TOOLS
Sheet1

Cell Formulas
Range	Formula
A2:D4,A6:D6	A2	=INDEX(FILTER($K:$U,($K:$K=$F2)*($L:$L<>""),""),SEQUENCE(COUNTIFS($K:$K,$F2,L:L,"<>")),{3,4,10,9})
Dynamic array formulas.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Sequence, the filter, and the Index
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[josvill2010]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Sequence, the filter, and the Index
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hi Guys, apologies for cross-posting without providing the link.
This is the link to where I posted the question.
The formula above was very close to be right but there was #n/a already in the file i provided that were basically confusing the formula when applying to the real file.

Link to the file where I posted the follow-up question on how to fix the formula.

Sequence, the filter, and the Index

Hi, it is my first attempt using a dynamic array formula. I would to bring purchase orders from another table by using the filter option The PO is linked to a specific GL code. I am trying to come up with a way that the formula brings all the PO's that are related to that GL code. My problem is...

chandoo.org