Search code examples
excelexcel-formulaexcel-365

$SPILL? Issue with Function to Match 2 Cells on a form to a Table and return a value from that table

I am making a form that if I choose Products from 2 cells, the function will Match the 2 cells to a database(table) that has Product Codes etc and their prices.

The issue I'm having is that I need to make it a Map/Lambda function and have it only on 1 row that I can Protect and Hide so that it doesn't get deleted by accident, as I'm not the only one that will be using the form.

Screenshot

Screenshot 2

My current function that I have is

=IFERROR(INDEX(Table55[Price],MATCH(1,(Form!C4=Table55[Product])*(Form!D4=Table55[Variant]),0))*H4," ")

The issue is, I have to copy that function to every cell which will cause issues with other people deleting the cell by accident and basically destroying all the other cells that rely on all cells functioning correctly.

I Tried

=MAP(C4:C20,D4:D20,Table55[Product],Table55[Variant],Table55[Price],LAMBDA(FC,FD,CC,CD,CE,IF(AND(FC=CC,FD=CD),CE,"")))

but it keeps Spilling down over 100 rows which messes it up. Table55 which has all my Prices etc has over 100 products which I think is causing the spill.

The form is supposed to work like this:

  1. Type in Product Code or Choose Product-Variant from drop down menu
  2. If the code is Type it automatically switches Product and Variant
  3. Once Product and Variant is chosen, the Price column will go through the database and match the Price with the Product.
  4. Total Column will Figure out what the Product is either Copy the Qty column or Multiplies LengthxWidthxQTY and divide by 10^6
  5. Price will Multiply the Price it matched from the Database by the total column.
Solution

  • You could use this formula

    =FILTER(Table55[Price],ISNUMBER(MATCH(Table55[Product]&Table55[Variant],C4:C20&D4:D20,0)))*TOCOL(H4:H20,1)

    Screenshot illustrating results of suggested formula

    The formula is premised on

    • the Product/Variant combination on each row being valid per Table[55]
    • there being a valid number in the Total field of each row