If a cell contains specific text, then return a specific answer

[veronicaandtodd]

I'm trying to create a formula that will look for a size in a specific cell and then return a cost in another cell. For example, sizes S-XL cost $0; sizes 1X-3X cost $2.14; and sizes 4X-5X cost $4.28. This is what I came up with but it only works for sizes 1X-5X. The formula returns an answer of 4.28 for sizes S-XL. Any help would be appreciated!
=+LOOKUP(B11,{"S","M","L","XL","1X","2X","3X","4X","5X"},{0,0,0,0,2.14,2.14,2.14,4.28,4.28})

 

[kevin9999]

With the Lookup function, the values in the lookup vector must be in ascending order. Therefore:

Book1
	A	B
1	Size	Cost
2	S	0
3	M	0
4	L	0
5	XL	0
6	1X	2.14
7	2X	2.14
8	3X	2.14
9	4X	4.28
10	5X	4.28
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=LOOKUP(A2,{"1X","2X","3X","4X","5X","L","M","S","XL"},{2.14,2.14,2.14,4.28,4.28,0,0,0,0})

 

[veronicaandtodd]

With the Lookup function, the values in the lookup vector must be in ascending order. Therefore:

Book1
	A	B
1	Size	Cost
2	S	0
3	M	0
4	L	0
5	XL	0
6	1X	2.14
7	2X	2.14
8	3X	2.14
9	4X	4.28
10	5X	4.28
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=LOOKUP(A2,{"1X","2X","3X","4X","5X","L","M","S","XL"},{2.14,2.14,2.14,4.28,4.28,0,0,0,0})

That worked. Thanks so much!

 

[kevin9999]

Happy to help & welcome to the Forum

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option would be to put this shorter formula in the top cell and it will fill all the results for you without the need to copy it down.

24 03 11.xlsm
	A	B
1	Size	Cost
2	S	0
3	M	0
4	L	0
5	XL	0
6	1X	2.14
7	2X	2.14
8	3X	2.14
9	4X	4.28
10	5X	4.28
Lookup Cost

Cell Formulas
Range	Formula
B2:B10	B2	=LOOKUP(IFERROR(--LEFT(A2:A10,1),0),{0,1,4},{0,2.14,4.28})
Dynamic array formulas.