# anyone for a quickie?

#### thesproing

##### Board Regular
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
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

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.

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!

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.

Replies
2
Views
438
Replies
2
Views
353
Replies
5
Views
148
Replies
1
Views
1K
Replies
2
Views
191

1,218,593
Messages
6,143,375
Members
450,483
Latest member
santvik234

### 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?

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