IF Statement Question

[vrachimis]

I am stuck on an if statement formula. I hope someone can help.
I have a list of fruit options in cell A1. I need a formula that will display the price of each fruit selected in cell b1.
So If "Apples" Are selected from cell A1, the price that will be shown in B1 is 5
If "oranges" are selected from cell A1, the price that will be shown in B1 is 10
etc etc
Vlookup will not be possible to use in this case.
Thanks much in advance

 

[Drrellik]

https://www.mrexcel.com/forum/excel-questions/967615-excel-if-function-combination.html

 

[Peter_SSs]

Vlookup will not be possible to use in this case.

Why not?

How do we know what your list of options in A1 are and what the corresponding prices will be? Are Apples (5) and Oranges (10) the only two choices?

 

[vrachimis]

https://www.mrexcel.com/forum/excel-questions/967615-excel-if-function-combination.html

That's rather irrelevant but thanks anyway.

 

[EvonS]

Have you tried the Index Match combination? I use it all the time for the type of thing you are describing. Here's some instructions https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/.

 

[vrachimis]

Vlookup can not be used because of the structure of the file. I could get it done with Vlookup easily but i am looking for a way around that.
It does not really matter what the options are. What matters is that the options are in ONE cell (drop down list) and the result should be shown in a very specific cell which will be the same regardless of the option selected in cell A1. So let's assume that i have 5 options in my drop down cell, and i pick one option. I need to write a formula that will tell excel that based on my selection the price of that item is x and excel needs to display this number in a cell. Below is a formula i used for a similar case, but in the case instead of a drop down list we have a number range. It is rather long because of too many parameters but the idea is simple. If the user writes a number higher than 950, the number 120 is shown. etc etc etc.
=IF(D12>950,120,IF(D12>900,114,IF(D12>850,108,IF(D12>800,102,IF(D12>750,96,IF(D12>700,90,IF(D12>650,84,IF(D12>600,78,IF(D12>550,72,IF(D12>500,66,IF(D12>500,60,IF(D12>450,54,IF(D12>400,48,IF(D12>350,42,IF(D12>300,36,IF(D12>250,30,IF(D12>200,24,IF(D12>150,18,IF(D12>100,12,)))))))))))))))))))

 

[Toadstool]

I am stuck on an if statement formula. I hope someone can help.
I have a list of fruit options in cell A1. I need a formula that will display the price of each fruit selected in cell b1.
So If "Apples" Are selected from cell A1, the price that will be shown in B1 is 5
If "oranges" are selected from cell A1, the price that will be shown in B1 is 10
etc etc
Vlookup will not be possible to use in this case.
Thanks much in advance

I'm guessing it's the old "can't select columns to the left" in a VLOOKUP problem, but that doesn't matter.

Use INDEX and MATCH.

I assume you have your Data Validation List already established, let's say in column H rows 1 through 5, and you have the price next to the name in column I rows 1 through 5.
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"] H[/TD]
[TD="class: xl64, width: 64"] I[/TD]
[/TR]
[TR]
[TD="class: xl63"]Apples[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]Oranges[/TD]
[TD="class: xl63, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63"]Bananas[/TD]
[TD="class: xl63, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63"]Guava[/TD]
[TD="class: xl63, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl63"]Papaya[/TD]
[TD="class: xl63, align: right"]22[/TD]
[/TR]
</tbody>[/TABLE]

In A1 you have your dropdown selection so in B1 put:
=INDEX($I$1:$I$5,MATCH($A$1,$H$1:$H$5,0))

This will INDEX into column I and select the row number where the MATCH was made between A1 and H1 to H5.

 

[thisoldman]

For the first formula, you don't need any IFs:

=(A2="apples")*5 + (A2="oranges")*10

This works because Excel will convert TRUE to the integer one and FALSE to zero when you use the Boolean values in a mathematical expression.


There is an error in the second posted formula.

=if(d12>950,120,
 if(d12>900,114,
 if(d12>850,108,
 if(d12>800,102,
 if(d12>750,96,
 if(d12>700,90,
 if(d12>650,84,
 if(d12>600,78,
 if(d12>550,72,

 if(d12>500,66,
 if(d12>500,60,

 if(d12>450,54,
 if(d12>400,48,
 if(d12>350,42,
 if(d12>300,36,
 if(d12>250,30,
 if(d12>200,24,
 if(d12>150,18,
 if(d12>100,12,)))))))))))))))))))

All the nested IFs aren't necessary here either.
For D12 greater than 100 and less than 500, one formula that works is:
=INT(D12 / 50) * 6

For D12 between 550 and less than 1000:
=INT(D12 / 50 + 1) * 6

I don't know what you want if D12 equals 500 or if D12 is greater than or equal to 1000.

 

[Peter_SSs]

Vlookup can not be used because of the structure of the file.

1. What is it about the structure of the file that causes this? (Just trying to understand your circumstances so that the best solution can be suggested)

It does not really matter what the options are.

2. Well, it may. You have said assume 5 but I think that is just an example. If there could be 80 it might mean a different solution might be better.


3. You haven't specifically said either way, but is there a list of choices and their corresponding prices anywhere in the workbook or are they all going to be simply hard-coded into the formula like mine below (which would then need changing if any prices change or drop-down options added/removed)?

4. You seem adamant that VLOOKUP cannot be used. For your example of 5 options (prices not listed anywhere in the workbook), wouldn't this work? If not, can you provide further details?

Excel Workbook
	A	B
1	Bananas	2
Price