# Extract Dynamic Data From a Table

#### kmonkmol

##### Board Regular
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>

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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?

Last edited:
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

With formulae

Excel 2013/2016
ABCDEFGHIJKLMNOPQ
1Invoice No:INV/2019/0020
2Customer NameInvoice No:Prod-1Prod-2Prod-3Prod-4Prod-5Prod-6Prod-7Prod-8Prod-9Prod-10Products NameQty
3Customer-001INV/2019/00201030040010Prod-11
4Customer-002INV/2019/0021325-225111-Prod-33
5Customer-003INV/2019/00225422-29-58Prod-64
6Customer-004INV/2019/0023963-22-2-Prod-91
7Customer-005INV/2019/002471185------
8Customer-006INV/2019/002529323----
9Customer-007INV/2019/00261435----
10Customer-008INV/2019/002725168-----
11Customer-009INV/2019/002817389----2
12Customer-010INV/2019/00299642-1-56-
Schedule
Cell Formulas
RangeFormula
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.

Replies
5
Views
108
Replies
3
Views
142
Replies
7
Views
313
Replies
2
Views
96
Replies
5
Views
356

1,203,538
Messages
6,055,995
Members
444,839
Latest member
laurajames

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back