vlookup function

[KAYE04]

Is this possible on excel?
I have for example a file using vlook up function.

source file is like this
SI-1001 10
SI-1002 20
SI-1003 30
SI-1004 40
SI-1005 50
SI-1006 60


Now i need to look up this
SI-1001-1004 vlook up all making the result be 100, or sumif function may do
SI-1005-1006 vlook up all making the result be 110, or sumif function may do


thank you so much, if this is possible on the excel formula

 

[ravi2628]

Hi Kaye,

Please try this.

Book1
	A	B
1	SI-1001	10
2	SI-1002	20
3	SI-1003	30
4	SI-1004	40
5	SI-1005	50
6	SI-1006	60
7
8	SI-1001-1004	100
Sheet1

Cell Formulas
Range	Formula
B8	B8	=SUMPRODUCT((B1:B6)*((A1:A6)>=CONCATENATE("SI-",MID(A8,4,4)))*((A1:A6)<=CONCATENATE("SI-",RIGHT(A8,4))))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[KAYE04]

Hi Kaye,

Please try this.

Book1
	A	B
1	SI-1001	10
2	SI-1002	20
3	SI-1003	30
4	SI-1004	40
5	SI-1005	50
6	SI-1006	60
7
8	SI-1001-1004	100
Sheet1

Cell Formulas
Range	Formula
B8	B8	=SUMPRODUCT((B1:B6)*((A1:A6)>=CONCATENATE("SI-",MID(A8,4,4)))*((A1:A6)<=CONCATENATE("SI-",RIGHT(A8,4))))

THANKYOU SO MUCH

it works!!,
thankyou so much this is so much helpful.
the number & position of the parenthesis make sense. if you miss a single parenthesis the equation will be error.
thank you

 

[Peter_SSs]

Thanks for updating your version details.