need Help on data validation & lookup

[bcsyeung]

Hi there,

i got below price list to lookup airfare:

From To Round Trip Airfare (LC)
Sydney Perth AUD 650
Sydney Adelaide AUD 450
Sydney Brisbane AUD 300
Sydney Port Douglas AUD 450
Sydney Melbourne AUD 280
Sydney Perth AUD 650
Sydney New Zealand AUD 800
Melbourne New Zealand AUD 800
Melbourne Port Douglas AUD 650

And I use data validation to lookup the price such that when I select from Sydney to Perth from drop down list, it will give me the price of $650.

What if i select Melbourne and Perth? How to create an error message to alert that there is no such match?

Also, how to create lookup formula for price if I want to select from Sydney to New Zealand and Melbourne to Zealand?



appreicate for your help!

 

[Robert Mika]

Can you post your formula?
You can wrap it in IFERROR statment(in Excel 07/10/) or ISNA() or IFERRROR in Excel 03.
For the second part of the question you probably need second validation list and the add the prices together.

 

[bcsyeung]

Hi Robert,

I'm just using the generic vlookup formula to lookup price for Sydney, how ever, if I select Melbourne and New Zealand, I dunno what formula I should use to lookup the price.

I dun understand what you mean by second validation list. can u elaborate?

thanks

 

[Robert Mika]

Does your dropdown have all option available so its the same as the lookup table?

Like this:

Excel Workbook
	A	B	C	D	E
1	From To Round TRIP	(LC)
2	Sydney Perth	650.00
3	Sydney Adelaide	450.00			Sydney Adelaide
4	Sydney Brisbane	300.00			450.00
5	Sydney Port Douglas	450.00
6	Sydney Melbourne	280.00
7	Sydney Perth	650.00
8	Sydney New Zealand	800.00
9	Melbourne New Zealand	800.00
10	Melbourne Port Douglas	650.00
Sheet1

or you have the desinations from/to in different columns?