Any Excel experts?

Tulipone

Tulipone

Messages
2,863
Name
Chris
Edit My Images
Yes
I have no idea why I am having problems with this so grateful for any help.

I need to use a value from a word in a formula.

Example: Assessment x 100 = 50

Assessment is a drop down option but needs to have a value of 0.5.

Delivery (another drop down option) needs to have a value of 0.9.

Complete needs to have a value of 1.

There are loads of others but you get the idea.

I'm thinking that the word needs to be a variable but cannot see how to set a variable. Else the words need to be in a VLOOKUP.

Any thoughts gratefully received.
 
One Sheet 2 have words in Column A and values in Column B

Then do a V Lookup, and work from there. Sounds very straight forward to me....

Give me 2 secs, and I'll draft a demo
 
Done.... Works easy.

One issue I noticed is that you can't use the dropdown list to VLookUp against.

Send me your email, and I'll send you over my example.
 
I have Assessment at A2 and the look up at G2:H13 with the return expected at D2.

In D2 I have:
=vlookup(A2, G2:H13 , 2)

I am expecting the value in H3 to be returned but nothing.
 
Tulipone said:
I have Assessment at A2 and the look up at G2:H13 with the return expected at D2.

In D2 I have:
=vlookup(A2, G2:H13 , 2)

I am expecting the value in H3 to be returned but nothing.
Click to expand...

Matt-P said:
Try adding another ,0 at the end to force a true match - Excel can be a bit funny matching text.

=vlookup(A2, G2:H13 , 2 ,0)
Click to expand...

It's actually a "false" statement he's missing at the end of the VLOOKUP
 
Tulipone said:
I have Assessment at A2 and the look up at G2:H13 with the return expected at D2.

In D2 I have:
=vlookup(A2, G2:H13 , 2)

I am expecting the value in H3 to be returned but nothing.
Click to expand...
I have copied this exactly, literally Copy and Paste, and it works fine this end.
 
Are you using the words in column G as your custom Drop Down menu in column A? If so, then Excel doesn't like that, hence why I have the words appear twice in my screen shot.
 
Very many thanks for all your help with this. My apologies, I thought I put a thank up you up earlier.

It was driving me mad earlier. It just wouldn't work and I couldn't understand why. I knocked off for lunch, came back and binned then redid it and it worked fine. Go figure!

This was always about offering a view on risk and adding weighting to some items. After it was completed I decided that my existing unweighted pivot tables and charts was far more effective!

Thank you again.
 
Actually you don't need the vlookup function. You can define a cell as assessment delivery etc and put any value in those cells then u can reference your functions to assessment etc then it will pickup the value. And do the right calcualtions
 
I know this has been answered, but it would be much better to be explicit in the spreadsheet as to the calculations you are performing and not masking the calculations.
ie instead of having 3 columns:-
assessment,50,100
delivery,50,45

You will be better off having 4 columns that clearly show the calculation
assessment,2,50,100
delivery,0.9,50,45
(the second column using either a lookup or an if statement)

you could also include the factorial inside the first column (e.g. assessment (x2), delivery (x0.9) ) and extract the factorial from the cell via a text function

The end user will understand more clearly wha the spreadsheet is trying to do, and when you open up the spreadsheet in 6 months time, it is clear to you what you have done. Always try and build a spreadsheet with clarity.
 
You must log in or register to reply here.
Back
Top