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

[TABLE="width: 1060"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Invoice No:[/TD]
[TD="align: left"]INV/2019/0020[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Customer Name[/TD]
[TD="align: left"]Invoice No:[/TD]
[TD="align: left"]Prod-1[/TD]
[TD="align: left"]Prod-2[/TD]
[TD="align: left"]Prod-3[/TD]
[TD="align: left"]Prod-4[/TD]
[TD="align: left"]Prod-5[/TD]
[TD="align: left"]Prod-6[/TD]
[TD="align: left"]Prod-7[/TD]
[TD="align: left"]Prod-8[/TD]
[TD="align: left"]Prod-9[/TD]
[TD="align: left"]Prod-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Products Name[/TD]
[TD="align: left"]Qty[/TD]
[/TR]
[TR]
[TD="align: left"]Customer-001[/TD]
[TD="align: left"]INV/2019/0020[/TD]
[TD="align: left"] 1[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] 3[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] 4[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] 1[/TD]
[TD="align: left"] -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Prod-1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: left"]Customer-002[/TD]
[TD="align: left"]INV/2019/0021[/TD]
[TD="align: left"] 3[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] 5[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] 5[/TD]
[TD="align: left"] 1[/TD]
[TD="align: left"] 11[/TD]
[TD="align: left"] -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Prod-3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: left"]Customer-003[/TD]
[TD="align: left"]INV/2019/0022[/TD]
[TD="align: left"] 5[/TD]
[TD="align: left"] 4[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] 9[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] 5[/TD]
[TD="align: left"] 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Prod-6[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: left"]Customer-004[/TD]
[TD="align: left"]INV/2019/0023[/TD]
[TD][/TD]
[TD="align: left"] 9[/TD]
[TD="align: left"] 6[/TD]
[TD="align: left"] 3[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Prod-9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: left"]Customer-005[/TD]
[TD="align: left"]INV/2019/0024[/TD]
[TD="align: left"] 7[/TD]
[TD="align: left"] 11[/TD]
[TD="align: left"] 8[/TD]
[TD="align: left"] 5[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Customer-006[/TD]
[TD="align: left"]INV/2019/0025[/TD]
[TD][/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] 9[/TD]
[TD="align: left"] 3[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] 3[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Customer-007[/TD]
[TD="align: left"]INV/2019/0026[/TD]
[TD][/TD]
[TD="align: left"] 1[/TD]
[TD][/TD]
[TD="align: left"] 4[/TD]
[TD="align: left"] 3[/TD]
[TD="align: left"] 5[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Customer-008[/TD]
[TD="align: left"]INV/2019/0027[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] 5[/TD]
[TD="align: left"] 1[/TD]
[TD="align: left"] 6[/TD]
[TD="align: left"] 8[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Customer-009[/TD]
[TD="align: left"]INV/2019/0028[/TD]
[TD="align: left"] 1[/TD]
[TD="align: left"] 7[/TD]
[TD="align: left"] 3[/TD]
[TD="align: left"] 8[/TD]
[TD="align: left"] 9[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Customer-010[/TD]
[TD="align: left"]INV/2019/0029[/TD]
[TD="align: left"] 9[/TD]
[TD="align: left"] 6[/TD]
[TD="align: left"] 4[/TD]
[TD="align: left"] 2[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] 1[/TD]
[TD="align: left"] -[/TD]
[TD="align: left"] 5[/TD]
[TD="align: left"] 6[/TD]
[TD="align: left"] -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Invoice No:[/td][td=bgcolor:#DDEBF7]INV/2019/0020[/td][td]<<< you can select INV number here[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Attribute[/td][td=bgcolor:#DDEBF7]Sum of Value[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Prod-1[/td][td]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Prod-3[/td][td]

3​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Prod-6[/td][td]

4​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Prod-9[/td][td]

1​

[/td][td][/td][/tr]
[/table]
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.