Extract Dynamic Data From a Table

[kmonkmol]

Sir
I have a Table of Invoice records, But I'm trying to get data for individual Invoice number,
if I use INDEX or VLOOKUP formula that will get zero value also I need greater than zero products and quantity only. Please help me

														Invoice No:	INV/2019/0020
Customer Name	Invoice No:	Prod-1	Prod-2	Prod-3	Prod-4	Prod-5	Prod-6	Prod-7	Prod-8	Prod-9	Prod-10				Products Name	Qty
Customer-001	INV/2019/0020	1	-	3	-	-	4	-	-	1	-				Prod-1	1
Customer-002	INV/2019/0021	3	2	5	-	2	2	5	1	11	-				Prod-3	3
Customer-003	INV/2019/0022	5	4	2	2	-	2	9	-	5	8				Prod-6	4
Customer-004	INV/2019/0023		9	6	3	-	2	2	-	2	-				Prod-9	1
Customer-005	INV/2019/0024	7	11	8	5	-	-	-	-	-	-
Customer-006	INV/2019/0025		2	9	3	2	3	-	-	-	-
Customer-007	INV/2019/0026		1		4	3	5	-	-	-	-
Customer-008	INV/2019/0027	2	5	1	6	8	-	-	-	-	-
Customer-009	INV/2019/0028	1	7	3	8	9	-	-	-	-	2
Customer-010	INV/2019/0029	9	6	4	2	-	1	-	5	6	-

<tbody>
</tbody>

 

[KennyGreens]

Hi there, I'm somewhat confused, what information are you looking to get? With your VLOOKUP, what number would you expect?


Edit: Just reread your post, are you looking to do a VLOOKUP based on the invoice number to find which products have more than 0?

 

[sandy666]

with PowerQuery unpivot prod columns
then use PivotTable

Invoice No:	INV/2019/0020	<<< you can select INV number here
Attribute	Sum of Value
Prod-1	1​
Prod-3	3​
Prod-6	4​
Prod-9	1​

changing the names of columns is a cosmetic

 

[Fluff]

With formulae

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1															Invoice No:	INV/2019/0020
2	Customer Name	Invoice No:	Prod-1	Prod-2	Prod-3	Prod-4	Prod-5	Prod-6	Prod-7	Prod-8	Prod-9	Prod-10				Products Name	Qty
3	Customer-001	INV/2019/0020	1	0	3	0	0	4	0	0	1	0				Prod-1	1
4	Customer-002	INV/2019/0021	3	2	5	-	2	2	5	1	11	-				Prod-3	3
5	Customer-003	INV/2019/0022	5	4	2	2	-	2	9	-	5	8				Prod-6	4
6	Customer-004	INV/2019/0023		9	6	3	-	2	2	-	2	-				Prod-9	1
7	Customer-005	INV/2019/0024	7	11	8	5	-	-	-	-	-	-
8	Customer-006	INV/2019/0025		2	9	3	2	3	-	-	-	-
9	Customer-007	INV/2019/0026		1		4	3	5	-	-	-	-
10	Customer-008	INV/2019/0027	2	5	1	6	8	-	-	-	-	-
11	Customer-009	INV/2019/0028	1	7	3	8	9	-	-	-	-	2
12	Customer-010	INV/2019/0029	9	6	4	2	-	1	-	5	6	-
Schedule

Cell Formulas
Range	Formula
Q3	=IFERROR(INDEX($C$3:$L$12,MATCH($P$1,$B$3:$B$12,0),MATCH($P3,$C$2:$L$2,0)),"")
P3	{=IFERROR(INDEX($C$2:$L$2,SMALL(IF(INDEX($C$3:$L$12,MATCH($P$1,$B$3:$B$12,0),0)>0,COLUMN($C:$L)-COLUMN($C2)+1),ROWS($1:1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.