Vlookup combined with IF function

Hello everyone

I am facing a rather odd issue with the Vlookup formula for which i need your assistance.


I have 2 sheets in my workbook, "Factuur" for an invoice template and "Assortment" where i have a table with my products.



The product table is sorted alphabetically with unique names and is arranged in the following way
Column 1: Product Name
Column 3: Price per unit if order quantity for this product is lower than 12 (VE=1)
Column 4: Price per unit for 12 or more order quantity (VE=12)


What i need is a formula which will return the price per unit based on the order quantity

In my invoice sheet i used the following formula
=VLOOKUP(G21;Assortiment!A1:E277;IF(D21<12;3;4))
where
G21 is the product name
Assortiment!A1:E277 the table in the catalogue sheet
D21 order quantity

The problem: The price shown is correct when the order quantity cell is blank. As soon as i type a value i Always get the price VE=12 price, even if the order quantity is lower than 12.


The strange thing is that EXACTLY the same formula works perfectly for all order quantities when used in the same sheet as with the catalogue. . So i am only getting this problem in the invoice sheet.


Is there something that i am overlooking? And how can i fix this without having to combine invoice and catalogue in one sheet?

Thank you beforehand
Ioannis

You may need to add a 4th parameter to your Vlookup to tell Excel you want an exact match

Try:

=VLOOKUP(G21;Assortiment!A1:E277;IF(D21<12;3;4);0)

Can you upload your workbook and indicate which cells show the wrong answer and what the answer should be.

Your column D values are text not numbers... you will need to account for that in your formula:

=VLOOKUP(G21;Assortiment!A1:E277;IF(D21+0<12;3;4);0)

Hi,

Your column D quantities are text (which happen to look like numbers) whereas you're returning a column Number for the function.

Try
=VLOOKUP(G22,ASSORTIMENT,IF(D22*1<12,3,4),0)

Ideally you should always ensure the two values you're using are both text or both numbers.