Data Validation, VLookup or If ???????

[dragons_lair]

I have a spreadsheet which contains the following

C1 C2 C3 C4
C5 C6 C7
CountryCode, CategoryCode, Product Code, Product Description, $A, $US, $NZ

I am using Data Validation to Select the product code based on the category
code selected, and using VLookup to select the corresponding product
description.

My question is how can I get a price selected based on the CountryCode as
well.

 

[OssieMac]

Not sure that I really understand how your worksheet is set up but am I
correct in assuming that where you are looking up the prices you have 3
columns for the prices, $A, $US and $NZ. If so, you could insert a column
(Hide it if necessary) and use vlookup to insert a number beside the country
code. You can then reference that cell for the Col_index_number in vlookup.

If my assumption is not correct then maybe you can post some more sampling
of your lookup table/s.

 

[OssieMac]

Not sure that I really understand how your worksheet is set up but am I
correct in assuming that where you are looking up the prices you have 3
columns for the prices, $A, $US and $NZ. If so, you could insert a column
(Hide it if necessary) and use vlookup to insert a number beside the country
code. You can then reference that cell for the Col_index_number in vlookup.

If my assumption is not correct then maybe you can post some more sampling
of your lookup table/s.

 

[dragons_lair]

Sorry for the confusion. But I have 2 worksheets set up 1 worksheet contains
my lists and the prices are in 3 separate columns. The other worksheet
contains the data validation structures.

My objective is to produce a quotation document based on products the
customer has asked to be quoted on and each country has its own price band.

When using the worksheet I select the country, category and product code
from drop down lists. The product description is selected using vlookup and I
have also used the vlookup function to select currently only 1 price band. I
have looked at a few examples I found on the web and have tried
unsuccessfully to use INDEX and MATCH to try and select the correct price
band but due to my inexperience in creating formulas I am having a bit of
trouble.


Hopefully this makes things a bit clearer

 

[dragons_lair]

Sorry for the confusion. But I have 2 worksheets set up 1 worksheet contains
my lists and the prices are in 3 separate columns. The other worksheet
contains the data validation structures.

My objective is to produce a quotation document based on products the
customer has asked to be quoted on and each country has its own price band.

When using the worksheet I select the country, category and product code
from drop down lists. The product description is selected using vlookup and I
have also used the vlookup function to select currently only 1 price band. I
have looked at a few examples I found on the web and have tried
unsuccessfully to use INDEX and MATCH to try and select the correct price
band but due to my inexperience in creating formulas I am having a bit of
trouble.


Hopefully this makes things a bit clearer

 

[OssieMac]

Hi again,

Your quote: "I have also used the vlookup function to select currently only
1 price band"

Based on the above, the method I gave you should work. Instead of using the
column number in the vlookup to tell the formula what value to return, use a
cell value with the column number in it. You just need a table with the
country code and the appropriate column number. Use vlookup to insert the
number of the column in a cell based on your country code. Then use that cell
reference in lieu of the column number in the price vlookup formula.

 

[OssieMac]

Hi again,

Your quote: "I have also used the vlookup function to select currently only
1 price band"

Based on the above, the method I gave you should work. Instead of using the
column number in the vlookup to tell the formula what value to return, use a
cell value with the column number in it. You just need a table with the
country code and the appropriate column number. Use vlookup to insert the
number of the column in a cell based on your country code. Then use that cell
reference in lieu of the column number in the price vlookup formula.