Match only certain text

[Goddess]

Hi,

I need to auto populate the yellow cells (I2:L2) based on the table in A1:D4. I can't seem to find a way to match certain taxt in column A and row 1.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Van	Bus	Car					Van	Van light	Car dark	Car
2	Others	5	8	4					5	6	1	4
3	Blue light	6	4	9
4	Blue dark	8	7	1
Sheet1

Thanks for any help!

 

[Goddess]

Hi,

I need to auto populate the yellow cells (I2:L2) based on the table in A1:D4. I can't seem to find a way to match certain taxt in column A and row 1.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Van	Bus	Car					Van	Van light	Car dark	Car
2	Others	5	8	4					5	6	1	4
3	Blue light	6	4	9
4	Blue dark	8	7	1
Sheet1

Thanks for any help!

Forgot to mention, cells I1:L1 are dynamic, meaning that it will change. So, I will need the yellow cells to auto update whenever I change the data in I1:L1.

Thanks!

 

[JEC]

Pull this to the right

=LET(x,SEARCH(" ",I1&" ")+1,INDEX($B$2:$D$4,MATCH("*"&MID(I1,x,10)&"*",$A$2:$A$4,0),MATCH(LEFT(I1,x-2),$B$1:$D$1,0)))

 

[Goddess]

Thanks JEC! However, this formula would not work if there is an additional text, like van mini or van mini light, correct?

 

[JEC]

No it won't. I assumed that you did not have duplicate additionals

 

[Goddess]

I just noticed that some of the items' description are two words instead of one

Will try to work around it. Thanks so much for your help!