Need help with formula looking up a value

[RinorM]

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.

[AliGW]

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.

[RinorM]

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

[AliGW]

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

[RinorM]

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?

[AliGW]

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.

[RinorM]

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

[AliGW]

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.

[AliGW]

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.

[RinorM]

Yes that is correct. Thank you anyway my friend !

[Glenn Kennedy]

This will work:

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.

[AliGW]

Try this:

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

[RinorM]

Both formulas worked fine. Thank you very much boys.

[AliGW]

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

[RinorM]

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

[AliGW]

No worries! Have a good day.

[RinorM]

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

[Glenn Kennedy]

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

[RinorM]

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.

[Glenn Kennedy]

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.

[AliGW]

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

[RinorM]

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

[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:

Please Login or Register  to view this content.

Phew... Back to the veggies.

[AliGW]

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

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.

[Glenn Kennedy]

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

[RinorM]

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