Look UP formula

[Ecel Dumbo]

Hi Dear Friends

I have the below items on which i need to do a lookup to get corresponsing values from the lookup data

0115 Storage
011544 Retail
0B1310 Fruits

Look UP data is
T1-B-0L1050 43.90
T1-L1-0115 24.20
T1-L1-011544 362.40
T1-L1-0B122944 2.00
T1-L1-0B1310 647.60
T1-L2-021058 26.80

Desired Answers
24.2
362.4
647.6

Can we have a straight formula without creating helper columns ?

 

[Hui]

D2: =SUMPRODUCT((LEFT(A2,FIND(" ",A2)-1)=RIGHT($A$10:$A$15,LEN($A$10:$A$15)-FIND("-0",$A$10:$A$15)))*$B$10:$B$15)
Copy down

 

[Khalid NGO]

Hi,
I am not sure what do you mean by without creating helper columns.

are you looking for this?

=INDEX($B$10:$B$15,MATCH(SUBSTITUTE(A2,MID(A2,FIND(" ",A2),99),""),MID($A$10:$A$15,FIND("-",$A$10:$A$15,FIND("-",$A$10:$A$15)+1)+1,99),0))

With CSE

Regards,

Edit:
Oops, didn't noticed Hui Sir replied.

 

[Ecel Dumbo]

Thanks Hui and Khalid. Both formula worked. By helper columns I meant, we modify existing data to separate what we are exactly looking for , example in T1-L1-0115, we are looking for 0115, so we create another column which shows 0115 and then do a vlookup sort of search. But both formulas worked without the need to do this. Thanks

 

[r1c1]

Good solutions @Hui and @Khalid NGO

Another one...

=VLOOKUP("*" & LEFT(A2,FIND(" ",A2)-1), $A$10:$B$15, 2, FALSE)

 

[Khalid NGO]

Good solutions @Hui and @Khalid NGO

Another one...

=VLOOKUP("*" & LEFT(A2,FIND(" ",A2)-1), $A$10:$B$15, 2, FALSE)

Wild card.... wow!
Good Idea Sir
here is one more:

=SUMIF($A$10:$A$15,"*"&LEFT(A2,FIND(" ",A2)-1),$B$10:$B$15)