Copper Contributor

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.



11 Replies


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?


See the attached version. you try to do the am trying your formula but its not working


What exactly is the problem? "The formula is not working" doesn't tell me much.

can you try to put formula to this. i tried your formula but not working.



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're just shooting  yourself in the foot.


See the attached and compare this with the original by Hans.


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)),"")

@HansVogelaar i really appreciate the help.
i provided the actual sample file. i hope you can help me out to put the formula on it.

i need to get the value auto of the one encircled in black once i put the name in column E.

the value is located in other sheets with multiple names .Thank you in advance.



Here is the link for the file. 



See the attached version.