Hi, I have two workbooks called "A"(Source) and "VLOOKUP_A"(Destination) as shown in the XLBB format.
Basically in the VLOOKUP_A workbook, I would like to apply VLOOKUP formula to extract info from the source workbook "A".
However, the formula has to be able to recognize the correct date and then only can further take the rest of the column data following the correct date. As the headers after the date are the same as the others, only the date header will be changing every week when new columns are added. Would like to learn how can I use VLOOKUP to do what I want or if there is other better alternative solution for my case. Thank you.
Basically in the VLOOKUP_A workbook, I would like to apply VLOOKUP formula to extract info from the source workbook "A".
However, the formula has to be able to recognize the correct date and then only can further take the rest of the column data following the correct date. As the headers after the date are the same as the others, only the date header will be changing every week when new columns are added. Would like to learn how can I use VLOOKUP to do what I want or if there is other better alternative solution for my case. Thank you.
| A.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | 10 | 61% | 47% | 40% | ||||||||||
| 2 | STORE | Volume Stock | VIP URGENT | 07-Mar | PC | NGPM | 28-Mar | PC | NGPM | 04-Apr | PC | NGPM | ||
| 3 | A | 0 | 0 | 48% | 78% | 40% | 1% | 34% | 45% | 48% | 15% | 72% | ||
| 4 | B | 0 | 1 | 97% | 43% | 42% | 86% | 29% | 19% | 31% | 94% | 7% | ||
| 5 | C | 0 | 0 | 17% | 25% | 16% | 73% | 89% | 1% | 97% | 21% | 93% | ||
| 6 | D | 1 | 0 | 67% | 69% | 52% | 43% | 51% | 14% | 21% | 14% | 46% | ||
| 7 | E | 0 | 1 | 72% | 50% | 98% | 93% | 2% | 10% | 18% | 46% | 59% | ||
| 8 | F | 0 | 0 | 20% | 88% | 45% | 6% | 9% | 50% | 7% | 41% | 60% | ||
| 9 | G | 1 | 1 | 86% | 65% | 64% | 62% | 78% | 76% | 41% | 50% | 89% | ||
| 10 | H | 1 | 0 | 33% | 4% | 65% | 52% | 84% | 48% | 9% | 56% | 16% | ||
| 11 | I | 1 | 0 | 82% | 59% | 7% | 40% | 65% | 92% | 68% | 16% | 54% | ||
| 12 | J | 0 | 1 | 92% | 20% | 2% | 13% | 34% | 2% | 64% | 8% | 17% | ||
ANALYSIS | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A1 | A1 | =COUNTIF(A3:A100,"*") |
| D1,J1,G1 | D1 | =AVERAGE(D3:D12) |
| B3:L12 | B3 | =RAND() |
| MASTER.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | 10 | |||||||||||
| 2 | STORE | 07-Mar | PC | NGPM | 28-Mar | PC | NGPM | 04-Apr | PC | NGPM | ||
| 3 | A | |||||||||||
| 4 | B | |||||||||||
| 5 | C | |||||||||||
| 6 | D | |||||||||||
| 7 | E | |||||||||||
| 8 | F | |||||||||||
| 9 | G | |||||||||||
| 10 | H | |||||||||||
| 11 | I | |||||||||||
| 12 | J | |||||||||||
VLOOKUP-A | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A1 | A1 | =COUNTIF(A3:A100,"*") |
