Forum Discussion
Index or Lookup? Subtraction and conditional formatting.
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) - D3Thank you, it works!
I just have an error in regards to the value in colmn E3 now:
Fault: Invalid name
You may visit X.ZOEKEN-functie - Microsoft Ondersteuning page, at very left bottom change the language on one you use
If back to English it will be shown as XLOOKUP function - Microsoft Support
