Extraction of Date on the Left

[JERRY89]

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.

[HansV]

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.

[JERRY89]

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

[HansV]

How about this then?

=INDEX(Sheet2!$B$1:$B$500,MATCH(A2&"*",Sheet2!$A$1:$A$500,0))

[JERRY89]

Hi Mr Hans,

That's work great...Thanks alot. Happy to learning and get advice from you

[JERRY89]

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

[HansV]

Could you post a small sample workbook? Your description is confusing to me.

[JERRY89]

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;

[HansV]

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:

Test Formula.xlsx

[JERRY89]

Hi Mr Hans,

Thanks Ya!! Problem Solved .....