Hi All,
I do want using vlookup to search for my data linkage but it cannot be done successfully as for example column A2=9874 ,Column B2=System 32 and column C is where i needed the amount using vlookup then in sheet2 column A=9874-System 32,and column B is amount where i needed in sheet1.
Extraction of Date on the Left
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extraction of Date on the Left
If I understand correctly, you can enter the following formula in C2:
=VLOOKUP(A2&"-"&B2, Sheet2!$A$1:$B$500, 2, FALSE)
Adjust the range A1:B500 as needed. The formula can be filled down.
=VLOOKUP(A2&"-"&B2, Sheet2!$A$1:$B$500, 2, FALSE)
Adjust the range A1:B500 as needed. The formula can be filled down.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Extraction of Date on the Left
Hi Mr Hans,
Thanks for the reply but it is not what i really wanted,i just want to find the data in A2 to Vlookup the data in sheet2 but in sheet2 column A is the problem where all the code is having a combination of number and material 9874-System 32 where my material code is remain unchangeable four digit only so i tried to use this formula VLOOKUP(A2, Sheet2!(LEFT($A$1:$A$500,4), 2, FALSE).
Thanks for the reply but it is not what i really wanted,i just want to find the data in A2 to Vlookup the data in sheet2 but in sheet2 column A is the problem where all the code is having a combination of number and material 9874-System 32 where my material code is remain unchangeable four digit only so i tried to use this formula VLOOKUP(A2, Sheet2!(LEFT($A$1:$A$500,4), 2, FALSE).
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extraction of Date on the Left
How about this then?
=INDEX(Sheet2!$B$1:$B$500,MATCH(A2&"*",Sheet2!$A$1:$A$500,0))
=INDEX(Sheet2!$B$1:$B$500,MATCH(A2&"*",Sheet2!$A$1:$A$500,0))
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Extraction of Date on the Left
Hi Mr Hans,
That's work great...Thanks alot. Happy to learning and get advice from you
That's work great...Thanks alot. Happy to learning and get advice from you
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Extraction of Date on the Left
Hi Mr Hans,
Is it possible from this formula =INDEX(Sheet2!$B$1:$B$500,MATCH(A2&"*",Sheet2!$A$1:$A$500,0)) be more analytical because i found out that is in my sheet2 column A it will have the data such as 9874-1 System32 where there might have -2,-3 and so on to represent different condition so in my sheet1 column A is 9874 as my main criteria of searching data in sheet2 and if possible can i add in one more criteria that is currency in sheet1 Column=$D$2, so in this situation how can i expand my formula above to do such function because i need to sum up all 9874 material amount based on this code and currency
Is it possible from this formula =INDEX(Sheet2!$B$1:$B$500,MATCH(A2&"*",Sheet2!$A$1:$A$500,0)) be more analytical because i found out that is in my sheet2 column A it will have the data such as 9874-1 System32 where there might have -2,-3 and so on to represent different condition so in my sheet1 column A is 9874 as my main criteria of searching data in sheet2 and if possible can i add in one more criteria that is currency in sheet1 Column=$D$2, so in this situation how can i expand my formula above to do such function because i need to sum up all 9874 material amount based on this code and currency
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extraction of Date on the Left
Could you post a small sample workbook? Your description is confusing to me.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Extraction of Date on the Left
Hi Hans,
Sorry ya..I have created a sample workbook as below..
https://www.dropbox.com/s/bukpqjuo82mo3 ... .xlsx?dl=0" onclick="window.open(this.href);return false;
Sorry ya..I have created a sample workbook as below..
https://www.dropbox.com/s/bukpqjuo82mo3 ... .xlsx?dl=0" onclick="window.open(this.href);return false;
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extraction of Date on the Left
In C2 on Sheet1:
=SUMIFS(Sheet2!$B$2:$B$500,Sheet2!$A$2:$A$500,A2&"-*-"&B2,Sheet2!$C$2:$C$500,D2)
Change 500 to a number that is greater than or equal to the last used row number, then fill down. See the attached version:
=SUMIFS(Sheet2!$B$2:$B$500,Sheet2!$A$2:$A$500,A2&"-*-"&B2,Sheet2!$C$2:$C$500,D2)
Change 500 to a number that is greater than or equal to the last used row number, then fill down. See the attached version:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Extraction of Date on the Left
Hi Mr Hans,
Thanks Ya!! Problem Solved .....
Thanks Ya!! Problem Solved .....