Forum Discussion

Copper Contributor

Oct 17, 2023

Solved

subtract filtered data - need help with formula

Hello. I need some help 🙂 I want a functional formula in column E (difference) where, after filtering by machine in column B (machine no), the program will recognize and subtract hou...

excel

Formulas and Functions

HansVogelaar
Oct 18, 2023
Andrej_J

LOOKUP and VLOOKUP are limited compared to XLOOKUP.

Try this in E2. It is an array formula, to be confirmed by pressing Ctrl+Shift+Enter.

Then fill down.

=IF(MAX((B$1:B1=B2)*ROW(B$1:B1))=0,"",D2-INDEX(D$1:D1, MAX((B$1:B1=B2)*ROW(B$1:B1))))

Andrej_J

Copper Contributor

Oct 18, 2023

I'm using Excel 2016 Professional Plus. However, when I enter this formula with corrections ( ; insted of ,) I end up with an empty cell, probably because I don't have the XLOOKUP function.

I tried using the LOOKUP function in the direction you were thinking, but I didn't succeed.

HansVogelaar

MVP

Oct 18, 2023

Andrej_J

LOOKUP and VLOOKUP are limited compared to XLOOKUP.

Try this in E2. It is an array formula, to be confirmed by pressing Ctrl+Shift+Enter.

Then fill down.

=IF(MAX((B$1:B1=B2)*ROW(B$1:B1))=0,"",D2-INDEX(D$1:D1, MAX((B$1:B1=B2)*ROW(B$1:B1))))

Andrej_J
Copper Contributor
Oct 18, 2023
Thank you very much! I owe you a beer, at the very least 🙂 Thanks once more. I'll make an effort to learn from this and seek less assistance next time.

Warm regards!