IF OR... or something else?

[Tazzy666uk]

Hi, It took me quite a while to work out one part of what i want, but could really do with some help now please

So, I have a drop down box with 4 'words' that someone selects:

Pre-school
School
Club
Other

What i want to happen is for someone to select one of these words and it bring up a price in another cell. I have another sheet that has costs in it along with other forms and boxes etc. For example

Pre-school =£5.00
School =£10.00
Club = £15.00
Other =£20.00

I created this for 'Pre-school' =IF(C7="Pre-School",Lists!H10,"") and it works.
I created this for 'School' =IF(C7="School",Lists!H11,"") and it works.
I created this for 'Club' =IF(C7="Club",Lists!H12,"") and it works.
I created this for 'Other' =IF(C7="Other",Lists!H13,"") and it works.

BUT...

How can i string them all together so that either 'word' returns the appropriate value within the same cell? I think this needs 'OR' in it somewhere but i am not clever enough to work it out!!


FYI - These costs can change quite often , which is why i have a single reference point in another sheet. I only have to change this figure, and the rest will change automatically.


I hope that makes sense?

Thanks in advance

Tazzy

 

[mrshl9898]

=VLOOKUP(C7,Lists!G10:H13,2,0)

Assuming column G has the words "Schhol" etc

 

[Toadstool]

Book1
	C	D	E	F	G	H
1					Type	Price
2					Pre-School	5
3					School	10
4					Club	15
5					Other	20
6
7	School	£10
Sheet1

Cell Formulas
Range	Formula
D7	D7	=INDEX($H$2:$H$5,MATCH($C$7,$G$2:$G$5,0))

Cells with Data Validation
Cell	Allow	Criteria
C7	List	=$G$2:$G$5

 

[Tazzy666uk]

Thank you both, i REALLY appreciate this. Can i ask - is one method better/more beneficial than the other?

thank you again, i appreciate your time

Tazzy

 

[Toadstool]

Hi Tazzy666uk,

VLOOKUP is easier to remember as it's only one function, but it does have limitations (you can't retrieve from columns on the left of the search column, you can only search vertically down columns as you'd need to use HLOOKUP to search horizontally).

There's no significant difference in speed of operation but if you're planning to progress your Excel knowledge then I'd suggest using INDEX and MATCH as they're more easily incorporated with other functions.