Changes VLOOKUP values based on two Drop Down (Data Validation) Selections

**EssexKiwi** · 02-08-2014, 08:07 AM

Hi everyone,

I have 4 price lists - they all have the same products and codes but are in different currencies. I would like to consolidate these into one and then display the correct price based on Data Validation drop down containing GBP, USD, Euro and RAND. Cell G2 (also named as Currency_Selected)

I have a table [Prices] in one sheet with product code, name and then 4 columns with price in each currency. On the second sheet I have already worked out to create a data validation drop list using the Product_Code - then using VLOOKUP I have populated the name and price in GBP (column 5) =VLOOKUP($A16,Prices,5,FALSE). I am assuming I need to somehow use and IF command to also bring in the Currency_Selected in order to to select the another column eg column 5 for USD.

I have tried some nested IF statements but just cant seem to get it working. Any advice appreciated - even a different approach.

Thanks.

**etaf** · 02-08-2014, 08:16 AM

GBP, USD, Euro and RAND

= IF ( G2= "GBP", VLOOKUP($A16,Prices,5,FALSE), if( G2="USD", VLOOKUP($A16,Prices,???,FALSE), if( G2="Euro", VLOOKUP($A16,Prices,???,FALSE), VLOOKUP($A16,Prices,???,FALSE) )))

where ??? is the column number for the different currencies

thats one way - there are others - see how that works first

i'm also assuming as you only have 4

GBP, USD, Euro and RAND

that if when
RAND is selected
and we have an IF for the other three
GBP, USD, Euro

then i have just defaulted to
RAND

hence no IF testing for RAND

**Richard Buttrey** · 02-08-2014, 08:24 AM

Hi,

Presumably you will have two drop down cells, one for Product Code and another for Currency.
Assuming your Prices table starts in A1 with column labels in row 1 then use a helper cell to identify which column of the table contains the drop down you've selected. So assuming B16 is the currency drop down in say C16 enter

=MATCH(B16,TableSheet!1:5,False)
Name this C16 cell say "Currency"

Then your vlookup is

Formula:

Please Login or Register  to view this content.

**EssexKiwi** · 02-09-2014, 07:11 AM

Price List.xlsx
Great - thanks this seems to have worked. I had been try to do the other way around with the VLOOKUP 1st then if statements inside.
Couple of additional questions;
1 - Is there a way to reference the table column in the VLOOKUP rather than a column number? eg in VLOOKUP($A16,Prices,5,FALSE) can I use Prices[USD - License] instead of column 5?
2 - Can I set the number formatting based on the currency selected eg i have used the following - if Currency_Selected="USD$" then use accounting format $ United States which is _-[$$-409]* #,##0.00_ ;_-[$$-409]* -#,##0.00 ;_-[$$-409]* "-"??_ ;_-@_ [when I look in custom format]

=IF(Currency_Selected="USD$",VLOOKUP($A5,Prices,6,FALSE),IF(Currency_Selected="GBP£",VLOOKUP($A5,Prices,9,FALSE),IF(Currency_Selected="EURO",VLOOKUP($A5,Prices,12,FALSE),IF(Currency_Selected="RAND",VLOOKUP($A5,Prices,15,FALSE),0))))

**Richard Buttrey** · 02-09-2014, 08:13 AM

Hi,

Re 1. Short answer is no. the 3rd element of the lookup needs to be a number which is why I suggested you use the MATCH & INDEX formulae. There was a typo in my original. Applied to your latest workbook it would be
=MATCH(F2,Prices!1:1,False)
but of course F2 needs to be in the first row of the Prices tab.

You seem to have reverted to the more complex set of IF functions

Re 2. Have you tried conditional formatting?

I've taken the liberty of slightly modifying your workbook and avoiding the IF functions, and adding the conditional formats. See attached