Forum Discussion
Xlookup or Index match
Hello, I am needing help with a formula and I just cannot get it quite right. I have tried Xlookup, Index Match, Vlookup and something is just not quite right. I have this first table on a separate worksheet within my wookbook. I am wanting to find the Sume_Earned_Value on a specific date (4/1/2022 and 4/2/2022) for each WorkCenter.
WorkCenter ProdDate Sume_Earned_Value
| FBD 2 | 4/1/2022 | 501.2568 |
| FEB B | 4/1/2022 | 7523.8978 |
| FFV 1 | 4/1/2022 | 1940.4482 |
| FGB 1 | 4/1/2022 | 3353.3836 |
| FGN | 4/1/2022 | 1894.7309 |
| FGP 1 | 4/1/2022 | 5722.4805 |
| FGQ 1 | 4/1/2022 | 54.2294 |
| FBD 2 | 4/2/2022 | 1104.039 |
| FEB B | 4/2/2022 | 4005.561 |
| FFV 1 | 4/2/2022 | 843.282 |
| FGB 1 | 4/2/2022 | 892.8009 |
| FGN | 4/2/2022 | 5588.6512 |
| FGP 1 | 4/2/2022 | 1070.012 |
| Work Center | SUPV | Desc | Date |
| 4/1/2022 | |||
| FBD 2 | BAY 1 | 985B/241B/881A/051A LENS-BEZEL ASSY | |
| FEB B | BAY 1 | 010B CHL AFS/HG/SPORT, LH/RH | $ |
| FFV 1 | BAY 1 | 985B/241B Base HL | $ |
| FGB 1 | BAY 1 | 985B/241B REFL B SUBASY | $ |
| FGN | BAY 1 | Pes sub | |
| FGP 1 | BAY 1 | U540 HL | |
| FGQ 1 | BAY 1 | 214 HL |
| Work Center | SUPV | Desc | Date |
| 4/2/2022 | |||
| FBD 2 | BAY 1 | 985B/241B/881A/051A LENS-BEZEL ASSY | |
| FEB B | BAY 1 | 010B CHL AFS/HG/SPORT, LH/RH | $ |
| FFV 1 | BAY 1 | 985B/241B Base HL | $ |
| FGB 1 | BAY 1 | 985B/241B REFL B SUBASY | $ |
| FGN | BAY 1 | Pes sub | |
| FGP 1 | BAY 1 | U540 HL | |
| FGQ 1 | BAY 1 | 214 HL |
See the attached sample workbook.
6 Replies
Hello Lorenzo
Thank you very much. But how do I copy that down and then on to the next worksheet. So, my workbook contains 31 tabs, one for everyday, a summary tab and then my query tab with the data needed for each days earned value. How do I copy that formula down and then into each day (tab)? Could I use a VBA code to do it for me everyday since my query pulls the data everyday?
See the attached sample workbook.
- You may use Sumproduct.
=Sumproduct(
(Table1[WorkCenter]="FBD 2")*(Table1[ProdDate]="4/1/2022")*
Table1[Sume_Earned_Value]
)
*Don't forget to lock the cell ref of "4/1/2022" in the formula with $ sign.
For e.g $D$2.- I tried your formula but I could not get it to work. I guess I didn't understand where to lock in my cell ref of D2 because I do not see it in your example. I know a lil bit about excel but still learning formulas I have not used.
