Need help with formula looking up a value

1. 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.``

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. 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.

3. 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

4. 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. 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?

6. 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.

7. 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. 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. Re: Need help with formula looking up a value

Originally Posted by RinorM
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. Re: Need help with formula looking up a value

Yes that is correct. Thank you anyway my friend !

11. Re: Need help with formula looking up a value

This will work:

Formula:
`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. 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. Re: Need help with formula looking up a value

Both formulas worked fine. Thank you very much boys.

14. 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. Re: Need help with formula looking up a value

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

16. Re: Need help with formula looking up a value

No worries! Have a good day.

17. 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. 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. 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. 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. 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. 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. 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:
`Please Login or Register  to view this content.`

Phew... Back to the veggies.

24. Re: Need help with formula looking up a value

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

Formula:
`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. 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. Re: Need help with formula looking up a value

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

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

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