anyone for a quickie?

[thesproing]

Hi guys,

real quickie...

is there an easier way of writing....

=IF(B2=A,X,IF(B2=B,Y,IF(B2=C,Zetc......ect......ect........0)))))))))))))))) (lol)

where a, b, c etc are years eg 2005, 2006, 2007, and x, y and z are cell referances? i have bout 40 varriants and im lazy.

thanks.

 

[dave3009]

There probably is some LOOKUP or INDEX or MATCH that might help but you will need to be more specific with Data layout and desired result.

KR


Dave

 

[Dan Waterloo]

I would use VLOOKUP in a situation like this.

Example: Set up your lookup table in the range A1:B3 of a sheet called LOOKUP_TABLE.

2005 Apples
2006 Oranges
2007 Bananas

Then use this formula in the cell where you had your complicated formula:
=VLOOKUP(B2, 'LOOKUP_TABLE'!$A$1:$B$3, 2, False)

Making the lookup table a named range would also simplify the formula.

 

[thesproing]

Hey

thanks, i think thats a start, but its just not working exactly as i had hoped.......

Although the VLOOKUP arangement is working for the first cell, i am applying the formulae to an array, and obviously every cell is just showing the same result,

ie, if "2006" is in B2, and "Banana" is next to it, every cell will display banana.

However, in my case "Banana" is refering to the first cell in an array on another sheet, so is there a way to "copy" a whole array using VlookUP;

So, here is the table using the lookup formula;

http://s188.photobucket.com/albums/z303/thesproing/?action=view&current=vlookup.jpg

this is the lookup table;

http://s188.photobucket.com/albums/z303/thesproing/?action=view&current=vlookuptable1.jpg

and this is the array it is refering to;

http://s188.photobucket.com/albums/z303/thesproing/?action=view&current=array.jpg

Obviously what i want is that when a certain year is entered into B2 the table on the same sheet should show the data from the corresponding sheet/year.

any and all help is greatly appreciated.

thanks again!

 

[Dan Waterloo]

Sending links to workbooks makes sense. Unfortunately, my corporate firewall is preventing me from seeing them. So, I'm not sure what you were saying.

The formula should change as you go down the list. Let's say you had this formula in C2 on your original sheet. I expect that you have a list of years in column B. Then in C3 the formula should be slightly changed to
=VLOOKUP(B3, 'LOOKUP_TABLE'!$A$1:$B$3, 2, False) so that it's doing the lookup on B3 rather than B2. You can get Excel to do the heavy lifting on making these small changes by dragging the formula down. I.e.,
1. enter the given formula into C2
2. select C2
3. hover your cursor over the bottom right hand corner of the cell so it changes from an thick plus sign to a thin plus sign
4. double-click that bottom right hand corner.

The formula will automatically fill in the entire length of your original list.

Hope this helps. If you are really trying to return an entire array for each value, I'm not usre what that means.