For Excel project, =IF(J12<150, 0, HLOOKUP(I12, C11:F20, J12/150, FALSE)), I would like to get the answer 3% but it is not working. I am trying to find the discount for the item that lark has bought, and the quantity that he has bought

Question

---

Moved from: Microsoft Copilot | Microsoft 365 Copilot | Development

Answer 1

Dear @Sheva cunin

Welcome to the Q&A Community! 

I understand that you'd like to have the result of 3% for Lark's Discount and can't achieve it with IF + HLOOKUP formula. May I know if you must use IF + HLOOKUP or you open to other possible formula?

In case another formula is acceptable, you can achieve your goal by using the following formula instead: =IF(J11<150,0,INDEX($C$12:$F$21,MATCH(J11,$B$12:$B$21,1),I11))  

Why this formula works better: 

• IF condition: If the quantity in column J is less than 150, the discount is 0. 
• MATCH: Finds the correct row based on the quantity purchased from the range B12:B21 (your minimum purchase requirements). 
• INDEX: Retrieves the discount from the matrix in C12:F21 using the row from MATCH and the product number in column I as the column index. 

This approach avoids common issues with HLOOKUP, such as header matching and row offset, and directly uses the product number as the column index. 

Let me know how it goes on your end or you need further clarifications, I am happy to assist you. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Answer 2

Hi,

As I can see, you have found your answer, but I recommend you use the following formula, which is a dynamic formula.

=IFNA(INDEX(C11:F20, XLOOKUP(J11:J16, B11:B20, SEQUENCE(ROWS(B11:B20))), I11:I16), 0)

or the following formula which is shorter and simpler compared to the previous one:

=IFNA(INDEX(C11:F20, XMATCH(J11:J16, B11:B20), I11:I16), 0)

Hope this helps.

IlirU