need Help on data validation & lookup

bcsyeung

New Member
Joined
Apr 21, 2011
Messages
23
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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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
 
Upvote 0
Does your dropdown have all option available so its the same as the lookup table?

Like this:
Excel Workbook
ABCDE
1From To Round TRIP(LC)
2Sydney Perth 650.00
3Sydney Adelaide 450.00Sydney Adelaide
4Sydney Brisbane 300.00450.00
5Sydney Port Douglas 450.00
6Sydney Melbourne 280.00
7Sydney Perth 650.00
8Sydney New Zealand 800.00
9Melbourne New Zealand 800.00
10Melbourne Port Douglas 650.00
Sheet1


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

Forum statistics

Threads
1,211,452
Messages
6,101,936
Members
447,764
Latest member
gopalgriffith

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