IF cell contains specific text enter this number question

U

uvdoombot

New Member
Joined
Jul 25, 2002
Messages
35
Code: 
=IF($B$14="My Generation Tour Adult Only",128,IF($B$14="Deluxe Tour",128,IF(AND($B$14="Jungle River Safari",$B$14="Power Boat Jeep Rental"),88,295)))

I have a pull down menu with 4 options. I'm attemping to make the prices in a cell change according to the text in cell B13.

The option Jungle river Safari still returns a price of 295 instead of 88. Where is my error?

Thank you.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Re: IF cell contains specific text enter this number questio

uvdoombot said:
Code: 
=IF($B$14="My Generation Tour Adult Only",128,IF($B$14="Deluxe Tour",128,IF(AND($B$14="Jungle River Safari",$B$14="Power Boat Jeep Rental"),88,295)))

I have a pull down menu with 4 options. I'm attemping to make the prices in a cell change according to the text in cell B13.

The option Jungle river Safari still returns a price of 295 instead of 88. Where is my error?

Thank you.
Click to expand...

Change the AND to OR

IF($B$14="My Generation Tour Adult Only",128,IF($B$14="Deluxe Tour",128,IF(OR($B$14={"Jungle River Safari","Power Boat Jeep Rental"}),88,295)))
 
Upvote 0
Re: IF cell contains specific text enter this number questio

could you not trim this even more to...

Code: 
IF(OR($B$14={"My Generation Tour Adult Only","Deluxe Tour"}),128,IF(OR($B$14={"Jungle River Safari","Power Boat Jeep Rental"}),88,295)))
{untested}

if your list is going to get any more populated, i might suggest a lookup formula - seeing as that your limited to seven nested IF functions in any one given computation.

hth

edit: typo
 
Upvote 0
Re: IF cell contains specific text enter this number questio

firefytr said:
could you not trim this even more to...

Code: 
IF(OR($B$14={"My Generation Tour Adult Only","Deluxe Tour"}),128,IF(OR($B$14={"Jungle River Safari","Power Boat Jeep Rental"}),88,295)))
{untested}

if your list is going to get any more populated, i might suggest a lookup formula - seeing as that your limited to seven nested IF functions in any one given computation.

hth

edit: typo
Click to expand...

Good catch Zack! :oops:
 
Upvote 0
Re: IF cell contains specific text enter this number questio

Strange. None of the formulas work. Everything put the Power Boat Jeep Rental choice is correct. It still gives me an amount of 88 when I select Power Boat Jeep Rental.
 
Upvote 0
hello uvdoombot (nice name),

this is what i got...
Book2
BCDE
14PowerBoatJeepRental88
15JungleRiverSafari88
16DeluxeTour128
17MyGenerationTourAdultOnly128
Sheet1


formula was the same. only one change was made: absolute row reference was taken out as so i could copy formula down.

are these not the desired results? how do they differ?
 
Upvote 0
Re: IF cell contains specific text enter this number questio

uvdoombot said:
Strange. None of the formulas work. Everything put the Power Boat Jeep Rental choice is correct. It still gives me an amount of 88 when I select Power Boat Jeep Rental.
Click to expand...

Check for any extra spaces before, between, or after you tours, both in the formula and in B14

=IF(OR($B$14={"My generation tour adult only","power boat jeep rental"}),128,IF(OR($B$14={"jungle river safari","jeep power boat rental"}),88,295))

try copying and pasting this
 
Upvote 0
Re: IF cell contains specific text enter this number questio

=IF(OR($B$14={"My Generation Tour Adult Only","Deluxe Tour"}),128,IF($B$14="Jungle River Safari",88,IF($B$14="Power Boat Jeep Rental",295,"")))

Regards,

Mike
 
Upvote 0
Re: IF cell contains specific text enter this number questio

It's worth to replace 3 IF's and one OR that you need (see Ekim's post) with one VLOOKUP...

=VLOOKUP(B14,{"Deluxe Tour",128;"Jungle River Safari",88;"My Generation Tour Adult Only",128;"Power Boat Jeep Rental",295},2,0)
 
Upvote 0
okay, so i see where i missed the last desired piece of the puzzle from the OP (the 128 bit), but how do you think these two formulas match up against each other aladin?...
Book2
BCDE
14PowerBoatJeepRental88295295
15JungleRiverSafari888888
16DeluxeTour128128128
17MyGenerationTourAdultOnly128128128
Sheet1


formula 1:

=VLOOKUP(B14,{"Deluxe Tour",128;"Jungle River Safari",88;"My Generation Tour Adult Only",128;"Power Boat Jeep Rental",295},2,0)

formula 2:

=LOOKUP(B14,{"Deluxe Tour","Jungle River Safari","My Generation Tour Adult Only","Power Boat Jeep Rental";128,88,128,295})

i've often wondered how these two would perform next to each other in like conditions, one syntax to another. any idea aladin?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
If cell contains specific text, then return a value
2 3
Replies
27
Views
2K
Sufiyan97
Sufiyan97
M
  • Question
Auto adjust row heights based on specific column
Replies
1
Views
200
XLfriend777
XLfriend777
B
  • Solved
Cell contains specific text, return the next 3 characters
Replies
5
Views
455
Phuoc
P
L
  • Question
How to extract records that contains a specific text words? 🙏🙌
2
Replies
10
Views
785
Lacan
L
Chandler8
  • Question
Adding a column that nullifies cells when something is entered in it
Replies
0
Views
483
Chandler8
Chandler8

Forum statistics

Threads
1,216,100
Messages
6,128,825
Members
449,470
Latest member
Subhash Chand

Share this page

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
Back
Top