Forum Discussion
Index or Lookup? Subtraction and conditional formatting.
Hi!
I would like to automatically calculate the remaining inventory based on the selected product and the quantity entered.
The inventory data is located in a separate table.
For example:
C3: Product
D3: Quantity
E3: Where the formula should be placed
I2:I24 : Range where inventory quantities are located
J2:J24 : Range where product names are located.
C3 is a dropdown-list based on range J2:J24.
The formula should do the following:
- Find the match of C3 in J2:J24
- Check the inventory quantity from the match of C3 in I2:24
- Subtract the amount in D3 with the inventory quantity
Adding to that I would like to add conditional formatting that if the value of E3 is smaller or equal to 0 than the inventory quantity (I2:24) of that same product (C3 and J2:J24) the color of the cell changes to red.
Is this possible? I tried with ChatGPT but unfortunately, the formulas I received are not correct or not applicable in my case, and I'm getting an error message (without explanation).
These are the codes I received from ChatGPT:
=INDEX(I:I, MATCH(C3, J:J, 0)) - D3
=IFERROR(INDEX('Sheet2'!$A$2:$A$100, MATCH(C3, 'Sheet2'!$B$2:$B$100, 0)) - D3, "")
=IFERROR(INDEX('Inhoud'!$A$2:$A$100, MATCH(C2, 'Inhoud'!$B$2:$B$100, 0)) - D2, "")
=IFERROR(VLOOKUP(C2, 'Inhoud'!$B$2:$A$100, 2, FALSE) - D2, "")
Thanks in advance for any help!
8 Replies
- that first formula looks like it should have worked. the others have ok concepts but wrong ranges
assuming you have a 365 then try
=XLOOKUP(C3, $J$2:$J$24, $I$2:$I$24, 0) - D3
if neither that 1st formula nor this formula work, please attach your sheet (no private/confidential info please) or upload to a drive and give a public link to the file so we can see why it isn't working for you.m_tarler both the first and your formula don't work either. I'm not sure if it's because the data is coming from a table or having the data validation active for one of the columns.
I sadly can't attacht a document here and due to policies cannot share the link, but I can provide screenshots, if that can help... I do in fact have Microsoft 365 for work. The Excel-version is 2406.
The dropdown-list comes from data validation on column J (Column I to K is a table).
This is the error I receive after trying the formula:
Roughly translated - "Problem found with this formula. Don't want to ype formula? If first item is = or -, Excel considers it as a formula,...." No extra information on why the formula contains an error. Same with the formulas from ChatGPT.
Thanks in advance again, hope this works without a link. If needed I can check regarding policies if there's another way.
- I see your from another country so my guess is that your settings use ";" instead of "," for the separator. try:
=XLOOKUP(C3; $J$2:$J$24; $I$2:$I$24; 0) - D3
