• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Look UP formula

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 ?
 

Attachments

  • Look UP Formula.xlsx
    11 KB · Views: 5
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.
 
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
 
Back
Top