excel vba - vlookup problem

[chief]

Here is the formula I have currently in cell J22

=IF(D22="","",VLOOKUP(D22,$N$1:$R$1459,IF($M$1="trade
sale",4,4),FALSE))

In cell M1 it changes from retail to trade depending on which they
choose at startup
In cell D22 is where they have a dropdown list to choose a product
In column N they have the product codes
In column O they have the product units
In column P they have the product
In column Q they have the trade price
In column R they have the retail price

If M1 is trade sale, it will return the proper trade price, however if
M1 is retail price, it still returns the trade price instead of the R
column retail price?

How can this formula be modified to compensate for this?

 

[Bob Phillips]

Try this

=IF(D22="","",VLOOKUP(D22,$N$1:$R$1459,($M$1="trade sale")*4+($M$1="retail
sale")*5,FALSE))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)