Possible Array formula?

[rfletcher35]

Hi Guys,

I'm sure this is a simple one i've done it before but many years ago. I have a drop down box containing months. I want another cell to display a certain figure from another location depending on the month chosen.

At the moment the cell reads =IF(J5="July",'Data Sheet'!B1)
Obviously I cannot keep putting a string of "IF's" along there but cannot remember the solution.

Can anyone help please...?

Thanks

Fletch

 

[RichardMGreen]

What sort of drop-down box is it?
How is the data stored in Data Sheet?

 

[rfletcher35]

2 columns A & B
A contains the months
B contains the figures

 

[Misca]

I believe the easiest way to solve this problem would be replacing the IF-formula with a lookup formula:

Make a two column table where the left column has the "July"s and other values and the right column houses the values or cell addresses you want to use with the match.

If the VLOOKUP returns a cell address (or named range) you need to use INDIRECT-function to tell Excel that it's a range, not text that it's returning).

 

[RichardMGreen]

What sort of drop-down box are you using?

 

[rfletcher35]

Hi,

Thanks for that I think it is Vlookup I need to use. Can you give me an example on how to use it as I can't get my head into it right now.

Thanks

Fletch

 

[Misca]

Here's a really good introduction to the VLOOKUP-formula: http://www.contextures.com/xlfunctions02.html

If it's the months that you're using as the lookup value and want to get the figures, you should be able to use your existing month / figure -table. And if it's text or exact matches that you're looking for remember to use the FALSE-parameter at the end.