one for the Excel gurus

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 🙂