IF cell contains specific text enter this number question

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.
 
Re: IF cell contains specific text enter this number questio

Set up a data table like this:
IF statement with OR.xls
ABCDEFGH
1MyGenerationTourAdultOnly128
2DeluxeTour128
3JungleRiverSafari88
4PowerBoatJeepRental295
5
Sheet1


Name the cells G:H as “Tours” (no quotes) with a dynamic range:
=OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$G:$G),2)

In your Validation drop down, which I assume you are using, use as the Source:
=INDEX(Tours,0,1)

In say C14:
=VLOOKUP(B14,Tours,2,FALSE)

The dynamic range feeds both the Validation drop down and the Vlookup function. If you add more tours in column G, with the corresponding price in column H, the drop down and the Vlookup will automatically pick up the addition (or deletions).

The main advantages of this routine:
Dynamic;
Avoids hard coding variables in the IF statement or a VLOOKUP statement, which is always prone to typos;
One data table feeds two functions.

If you prefer, use a macro to dynamically name the range:
Code:
Sub myRng()

With Sheet1
   .Range("G1", .Range("G65536").End(xlUp)(1, 2)).Name = "Tours"
End With

End Sub
Regards,

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

Ekim said:
Set up a data table like this:..
Name the cells G:H as “Tours” (no quotes) with a dynamic range:
=OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$G:$G),2)

I see some impact...

But why not take out the volatile OFFSET...

=Sheet1!$G$1:INDEX(Sheet1!$H:$H,COUNTA(Sheet1!$G:$G))

or better/faster:

=Sheet1!$G$1:INDEX(Sheet1!$H:$H,MATCH(9.99999999999999E+307,Sheet1!$H:$H))

Sorting G:H on G if possible would boost the performance further.

Note. In case anyone wonders: I did post in this thread to help the OP, confronted with a task like the one under consideration, to think in terms of retrieval rather than in terms of formulas with If.
 
Upvote 0
Re: IF cell contains specific text enter this number questio

Ok this works great
Code:
=IF(OR($B$14={"My generation tour adult only","Deluxe Tour"}),128,IF(OR($B$14={"jungle river safari","jeep power boat rental"}),88,295))
and so does

formula 2:
Code:
=LOOKUP(B14,{"Deluxe Tour","Jungle River Safari","My Generation Tour Adult Only","Power Boat Jeep Rental";128,88,128,295})
I wasn't familar with LOOKUP. Both work equally well for MY spreedsheet since I'll never have more than 4 activities per spreedsheet. I think I'll use LOOKUP anyway.

Thank you everyone for the help. I really appreciate it. =)
 
Upvote 0
Re: IF cell contains specific text enter this number questio

uvdoombot said:
Ok this works great
Code:
=IF(OR($B$14={"My generation tour adult only","Deluxe Tour"}),128,IF(OR($B$14={"jungle river safari","jeep power boat rental"}),88,295))
and so does

formula 2:
Code:
=LOOKUP(B14,{"Deluxe Tour","Jungle River Safari","My Generation Tour Adult Only","Power Boat Jeep Rental";128,88,128,295})
I wasn't familar with LOOKUP. Both work equally well for MY spreedsheet since I'll never have more than 4 activities per spreedsheet. I think I'll use LOOKUP anyway.

Thank you everyone for the help. I really appreciate it. =)

Are you implying that the VLOOKUP version does not work?
 
Upvote 0
Re: IF cell contains specific text enter this number questio

Aladin Akyurek said:
Are you implying that the VLOOKUP version does not work?

No not at all. I wrote the message when it was still at page one or so and then realized I just previewed and didn't post. (It was in another tab). Long story short I tried two of the solutions and found two that worked for me.

I didn't see the VLOOKUP to try it. Appreciate the help.
 
Upvote 0
Re: IF cell contains specific text enter this number questio

uvdoombot said:
Aladin Akyurek said:
Are you implying that the VLOOKUP version does not work?

No not at all. I wrote the message when it was still at page one or so and then realized I just previewed and didn't post. (It was in another tab). Long story short I tried two of the solutions and found two that worked for me.

I didn't see the VLOOKUP to try it. Appreciate the help.

I see... One word of caution, although hardly relevant here. Using LOOKUP or VLOOKUP with match-type set to 1/TRUE can be risky with text data. That's why I proposed VLOOKUP with match-type to 0/FALSE although I explicitly sorted the text entries.
 
Upvote 0

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