Index/Collect while meeting specific criteria and doing math

A C

Good afternoon community,

I'm in need of help with a formula that I feel has come close, but still just off of the mark. I have three sheets:

The first is holding static data (Inventory Cost sheet).

The second sheet (Tool Inventory) is where data comes into the sheet from a form and some math formulas do calculations based off of the 'Inventory Cost sheet'. I was doing all of the math on this sheet, but I now have too many columns and was forced to a third sheet.

The third sheet (Amount Owed) is where I have a formula to do the calculations for the cost of what is owed.

I have this formula that works, but I need it to be vehicle specific.

=INDEX(COLLECT({2025 Inventory Cost Sheet Range 2}, {2025 Inventory Cost Sheet Range 85}, HAS(@cell, "Harnesses - Check Date (4)")), 1) * SUM({2025 Coil Tool Inventory Range 2})

I have columns for the different vehicle types on both the 'Tool Inventory' and 'Amount Owed' sheets. The above formula needs to reference the 'Second Vehicle' column on the second and third sheets and if they match, then complete the formula above.

I have tried so many different formulas that have 'IF', 'IFERROR', nested 'IFERROR', 'MATCH'. Again, have come close but not exact.

A C

Well, no sooner did I post this and keep trying, when I figured it out. Here is the solution:

=IF(HAS({2025 Coil Tool Inventory Range 3}, [Vin Truck 2]@row), INDEX(COLLECT({2025 Inventory Cost Sheet Range 2}, {2025 Inventory Cost Sheet Range 85}, HAS(@cell, Harnesses - Check Date (4)")), 1)) * SUM({2025 Coil Tool Inventory Range 2})

A C

Well, no sooner did I post this and keep trying, when I figured it out. Here is the solution:

=IF(HAS({2025 Coil Tool Inventory Range 3}, [Vin Truck 2]@row), INDEX(COLLECT({2025 Inventory Cost Sheet Range 2}, {2025 Inventory Cost Sheet Range 85}, HAS(@cell, Harnesses - Check Date (4)")), 1)) * SUM({2025 Coil Tool Inventory Range 2})

Marcela

Thank you for posting your solution.

I'm glad you figured it out!