Give Numerical Value to Microsoft Forms response

[AhmedDandeni]

Hello,

This is really specific and confusing but hopefully someone will have at least an idea of an approach that might help. [You can skip this but I'm explaining the project] I intern at a non-profit and I'm working on making the donation receipts digital, right now this is ready and the PDF  receipt is created and sent to the donor and saved in our database, however the process after is to give a dollar value for the donation to be inputted and registered to the donor name.

Each item is registered in a different question (Example attached) and I'm trying to match it to an excel file that has every item and in a different column it has the dollar value for it, I want it to give all the item a dollar value and calculate the total. (After calculating that total it will be added to an a excel sheet but that's a different task).

I would be really thankful for any suggestions or ideas, it will be better if the solution does not require Power Automate Premium because I'm a non-paid intern and IT team refusing getting the subscription. 

Thansk!

[AhmedDandeni]

I forgot to mention, the pricing list have over 2500 item not just the 4

[takolota]

It would be much better if your form & your Excel had some unique identifier for the items.

That way you could use that to filter the Excel table.

If you can’t set that up, then you can still pull in all the Excel values in the List rows present in table action.

Then in a loop for each item in the donation, you could use a Filter array action on the Excel List rows output to filter to any rows where the Excel item column values contain certain words in the Item description from the form response. So you’ll probably split the Item description content on spaces, then take the 1st 2 words or the 1st & last word for the right-side of the Filter array conditional against the Excel Item column in the left side of the conditional.

Then you can check if it returned a single row (probably your result) or multiple rows (you’ll need to set it to notify someone to review the entry).

Once you have a single result, you can then use certain expression notation like

first(body(Filter_array))?[‘InsertPriceColumnName’] 

to get the price value for that row it filtered to.

Once each form response item has a price associated to it, you should be able to figure out how to add them in a loop pretty easily, maybe with an increment variable action with the increment amount set to the mul(quantity, price) of each response item or something.