HELP for EXCEL

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.

Prince0622_0-1697131178563.png

 

11 Replies

@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 

See the attached version.

https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/584627can you try to do the am trying your formula but its not working

@Prince0622 

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.



https://www.dropbox.com/scl/fi/e4q097d1gr7zylc2tazck/1697484979523_sample-file.xlsx?rlkey=u14k0olp7r...

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

@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.

PRINCED2023_0-1699285081177.png

 

Here is the link for the file.

 

https://www.dropbox.com/scl/fi/qzd3fmrbro9f8khr8057u/1699285261376_SAMPLE.xlsx?rlkey=251cnplzx6s6gsr... 

 

@PRINCED2023 

See the attached version.