can please someone help me to figure out what the formula for this.i need to get the value of the one encircled in black.the value is located in other sheets with multiple same names i tried Vlook up but when i paste another data with different date (ex Oct 3) it gets the same data for Oct 2.

Prince0622 I modified ever so slightly the formula that my friend, HansVogelaar , had given you. There was a discrepancy in your spreadsheets between how you referred to SAM SMITH, in the PROD RAW sheet you had "SAM SMITH" whereas in the "Daily View" sheet you had "SAM.SMITH" --that oh, so little period between the names. So HansVogelaar had worked with what you had, and used the SUBSTITUTE function to insert a period in the search criteria. I chose to delete the period and make it "SAM SMITH" in both instances. That also allowed a simpler formula. IF the period is necessary (which I seriously question) then you should have it both places; if it's not, then in neither. But don't mix them....you're just shooting yourself in the foot. See the attached and compare this with the original by Hans.

Prince0622 In the sample workbook that you made available earlier, the PROD RAW sheet had "SAM SMITH" (with a space between first and last name), while the Daily View sheet had "SAM.SMITH" (with a point between first and last name). My formula took that into account. In your new workbook, both sheets have a point between first and last name, so the formula should be slightly different. In L2 on the Daily View sheet: =IFERROR(INDEX('PROD RAW'!$K$2:$K$7023,MATCH(1,('PROD RAW'!$A$2:$A$7023=$B2)*('PROD RAW'!$D$2:$D$7023=$E2),0)),"")

Prince0622 Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

HansVogelaar here is the lionkhttps://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.dropbox.com%2Fscl%2Ffi%2F541m2a1w3cd0ak1m6t9e8%2FSAMPLE.xlsx%3Frlkey%3Dzlg4e19w56gnfwgjlznrpcpj8%26dl%3D0&data=05%7C01%7CPrinceDarl.Jamisola%40iqor.com%7Cfe32e1171a7b44c5027508dbcb4cd0e9%7C64d057c4c04c46f99cdf1d2ba46b5074%7C0%7C0%7C638327304114416276%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=qcES%2BEJA8mvnJQID%2BcwhLjdOArhMv%2FQCweGB6eHY5fg%3D&reserved=0

Prince0622 See the attached version.

HELP for EXCEL | Microsoft Community Hub

11 Replies

HansVogelaar
MVP
Oct 12, 2023
Prince0622

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- Prince0622
  Copper Contributor
  Oct 12, 2023
  HansVogelaar
  here is the lionk
  
  https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.dropbox.com%2Fscl%2Ffi%2F541m2a1w3cd0ak1m6t9e8%2FSAMPLE.xlsx%3Frlkey%3Dzlg4e19w56gnfwgjlznrpcpj8%26dl%3D0&data=05%7C01%7CPrinceDarl.Jamisola%40iqor.com%7Cfe32e1171a7b44c5027508dbcb4cd0e9%7C64d057c4c04c46f99cdf1d2ba46b5074%7C0%7C0%7C638327304114416276%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=qcES%2BEJA8mvnJQID%2BcwhLjdOArhMv%2FQCweGB6eHY5fg%3D&reserved=0
  - HansVogelaar
    MVP
    Oct 12, 2023
    Prince0622
    
    See the attached version.
    SAMPLE.xlsx111 KB

Forum Discussion

HELP for EXCEL

11 Replies

Resources