+ Reply to Thread
Results 1 to 26 of 26

Need help with formula looking up a value

  1. #1
    Forum Contributor
    Join Date
    04-28-2014
    Location
    Sverige
    MS-Off Ver
    Excel 2010
    Posts
    221

    Need help with formula looking up a value

    Hi! I need help to look up a value from a drop-down list in a set of range. I have been using this code:

    Please Login or Register  to view this content.
    See the attached excel file for more info: Book2.xlsx

    In cell C175 I have written this code where I want the LOOKUP value to appear. When you choose a value of for example HEA 260 (13%) in the drop-down list (Cell B1) it shows "N/A" in cell C175. And when you choose VKR400x400x16.0 (4%) it doesnt give the correct value in cell C175. Anyone with an idea on how to solve this?

    Thank you.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,357

    Re: Need help with formula looking up a value

    See if this does what you want:

    =VLOOKUP(LEFT($B$1,7),$A$175:$B$305,2,0)

    It's difficult to know, though, as you haven't told us what the resulting values should be.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Contributor
    Join Date
    04-28-2014
    Location
    Sverige
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Need help with formula looking up a value

    Hi Ali. The formula you gave me works for all values in the drop down list except for values long like this: VKR250x250x12.5 (12%)

    Sorry but the result will for example be: if the value in B1 is VKR250x250x12.5 (12%) this should give me a value of : 91.9
    Last edited by RinorM; 05-04-2014 at 04:23 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,357

    Re: Need help with formula looking up a value

    But in your formula, you are telling Excel to match just the first 7 characters of the value in B1!

  5. #5
    Forum Contributor
    Join Date
    04-28-2014
    Location
    Sverige
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Need help with formula looking up a value

    The formula some guy helped me to create so I dont really understand it. How can I make it to match all characters except for the percentege ones, in above example (12%)

    can I where you insert the number "7" change the range that it goes from 7-15?
    Last edited by RinorM; 05-04-2014 at 04:27 AM.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,357

    Re: Need help with formula looking up a value

    That's going to be quite tricky. Using the SUBSTITUTE function would be easy if it accepted wildcards, but it doesn't.

    What your original formula was saying is, "Look at the first seven characters of B1, find a match in the A range and return the corresponding value in the B range". What my offering added to this was that it should find an exact match, not an approximate one. All I can assume is that the person who provided the formula had not been shown all the possible options in your drop-down list.
    Last edited by AliGW; 05-04-2014 at 04:37 AM.

  7. #7
    Forum Contributor
    Join Date
    04-28-2014
    Location
    Sverige
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Need help with formula looking up a value

    But is there any way to go around this? For example to choose all characters except the one with the percentage?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,357

    Re: Need help with formula looking up a value

    As I have said, it will be tricky. Someone here will be able to help. If all the percentages were in double figures, it would be easy, but they are not.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,357

    Re: Need help with formula looking up a value

    Quote Originally Posted by RinorM View Post
    can I where you insert the number "7" change the range that it goes from 7-15?
    That wouldn't work. You want Excel to look at everything up to the parenthesis.

  10. #10
    Forum Contributor
    Join Date
    04-28-2014
    Location
    Sverige
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Need help with formula looking up a value

    Yes that is correct. Thank you anyway my friend !

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,778

    Re: Need help with formula looking up a value

    This will work:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    BUT.....

    Beware. Some text has extra spaces in it, e.g. CellA175 is IPEspacespace80.

    Beware IPE 80 isn't in your dropdown list.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,357

    Re: Need help with formula looking up a value

    Try this:

    =VLOOKUP(SUBSTITUTE(B1,(RIGHT(B1,LEN(B1)-FIND("(",B1)+2)),""),$A$175:$B$305,2,0)

  13. #13
    Forum Contributor
    Join Date
    04-28-2014
    Location
    Sverige
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Need help with formula looking up a value

    Both formulas worked fine. Thank you very much boys.
    Last edited by RinorM; 05-04-2014 at 05:01 AM.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,357

    Re: Need help with formula looking up a value

    You're welcome! Maybe you can't see my avatar, but I'm a girl ...

  15. #15
    Forum Contributor
    Join Date
    04-28-2014
    Location
    Sverige
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Need help with formula looking up a value

    Sorry no I can't. I apologise for the mistake!

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,357

    Re: Need help with formula looking up a value

    No worries! Have a good day.

  17. #17
    Forum Contributor
    Join Date
    04-28-2014
    Location
    Sverige
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Need help with formula looking up a value

    Sorry to bother you again Ali. But a smal issue has occured. If i instead have a name in cell b1 called "KRÍR D273(t12,5) (8%)" this gives me a N/A error in cell C183. The range for this lookupfunction is $A$175:$B$343. Thank you for your help.

    Book2.xlsx

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,778

    Re: Need help with formula looking up a value

    Both Ali's formula & mine work(ed) on the basis that we were looking for the first parenthesis to define the unique part of the cell range that you wanted to match. Tricky now that you have another set of parentheses...

  19. #19
    Forum Contributor
    Join Date
    04-28-2014
    Location
    Sverige
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Need help with formula looking up a value

    But if i change and take away the parenthesis of the new one, will it work then? Because I can take away the first parenthesis.

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,778

    Re: Need help with formula looking up a value

    Yes... And it would be much easier than the really messy equation that I've been playing with. Check it out & let me know. I must go away for a while & plant some veggies in the garden, before it rains.

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,357

    Re: Need help with formula looking up a value

    And he'll need to change the end cell of the range to match the extended range mentioned.

  22. #22
    Forum Contributor
    Join Date
    04-28-2014
    Location
    Sverige
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Need help with formula looking up a value

    I tried it and it worked very good. Thanks alot for the assistance you two

  23. #23
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,778

    Re: Need help with formula looking up a value

    If you do come across a circumstance where you'd like to keep the extra (first) set of parentheses, this will sort it:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Phew... Back to the veggies.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,357

    Re: Need help with formula looking up a value

    Quote Originally Posted by Glenn Kennedy View Post
    If you do come across a circumstance where you'd like to keep the extra (first) set of parentheses, this will sort it:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Phew... Back to the veggies.
    It just got you, didn't it? Even the veggies had to wait! :-D Hope the sun is as warm as it is here.

  25. #25
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,778

    Unhappy Re: Need help with formula looking up a value

    Sadly, no. Northern Ireland is dark black clouds from East to West. In fact, I'm sheltering from the rain right now �...

  26. #26
    Forum Contributor
    Join Date
    04-28-2014
    Location
    Sverige
    MS-Off Ver
    Excel 2010
    Posts
    221

    Re: Need help with formula looking up a value

    Haha!! Thanks for the help and have a good day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1