anyone for a quickie?

thesproing

Board Regular
Joined
Jul 16, 2007
Messages
76
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Jan 4, 2007
Messages
876
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

Board Regular
Joined
Jul 16, 2007
Messages
76
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

Well-known Member
Joined
Jan 4, 2007
Messages
876
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.
 

Forum statistics

Threads
1,181,053
Messages
5,927,848
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top