Switch

[mypig7]

I'm trying to learn some new formulas & have come across SWITCH.

I know how to use VLOOKUPS

I have a list of names of Fruit in Column A - in A1:A5 & a list of costs for each next to them in B1:B5

In D1 I want to type some fruit e.g. Apple, which is also in A3, the press Enter when I have finished typing.

Then in E1 I want a formula to look at D1, then go and look for it in A1:B5 find the fruit that I have typed in & bring back the cost, from column B.

I know VLOOKUP can do that.

From what I've read, so will SWITCH

What's the benefit of using SWITCH?

 

[Special-K99]

Last time I saw SWITCH mentioned it was a negative comment. I think it reasoned that using VLOOKUP on a range and modifying the range is more user-friendly than amending the SWITCH formula, and I agree with that.

 

[Fluff]

With switch you don't need a lookup table, so as long as the criteria never change, it's fine. But if your criteria are likely to change, then you would have to modify the formula every time.