MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Logic failure, can't think my way through this one
I have to pull a figure from another spreadsheet based on a text value from a table. Example
TEXT VALUE
January £100
February £200
March £150 and so on
If this month is January then I want £100. In my 2nd spreadsheet I type "January". I could do this with an IF command but it is going to have 12 variables so I assume there is a better way.
TIA
Kev
Could you not just take the last 4 digits from the cell if they always follow the format of MONTH £100?
Thanks
My posting didn't format very well. There is a column with text and then multiple columns with values. I need to be able to pick a value based on the text matching the entry on a separate page.
Hope that makes sense, maybe time to hit the coffee.
Kev
you want to take a look at the VLOOKUP command. It's pretty much designed to do what you want. Take a look at this tutorial...
http://office.microsoft.com/en-001/excel-help/vlookup-what-it-is-and-when-to-use-it-RZ101862716.aspx
Yep, vlookup is the answer there.
=VLOOKUP(resultssheet!A2,datasheet!A:B,2,FALSE)
in 'ResultsSheet'A2 would contain "January". As you copy the formula down it would look at B2 "February", C2 "March" etc.
To make things neater;
Assuming you will only ever enter the month into that cell then you can set up “data validation” which will give you a drop down list to pick the month from;
Data->Data Validation->Settings->Allow->List
If you use the month list from your lookup data as the “Source” input then you won’t have problems with typos.
Thanks all, knew the collective would be able to sort that for me 🙂