Formula to compare multiple cells and do a calculation

[steph1111]

Hello,

Issue:
I was looking for a formula that I can use that would look at 4 cells (in a row) on one sheet and compare them to 4 cells in another sheet, and if they match it would then look at a specific column on one sheet and take that number and multiply it by the number in a specific column on sheet two and display the results on sheet one.

For example:
I'm looking for the words apple, orange, berry and kiwi to be in the same row in both sheets (side by side columns) and if that is true then it would take cell 5 in column X of sheet one and multiply it by cell 5 of column X of sheet two and display it in column Y of sheet one

Notes:
We have over 1000 rows of data to go through so using a nested if statement would be very long to create and tedious. I don't know if there is something that can be done with arrays as I'm not sure if a look up with help.


Thank you all for your help

[JohnTopley]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[steph1111]

Hello John,

I have added the file to my original message. Essentially I'm looking for Apples Oranges Berries Kiwi to be present in both lists in the exact same order in the same row and if so to take the value in sheet one and two and multiply them and then add that value to outcome. In my example the answer would be 5

Hope this helps someone help me...I'm really stuck

[FlameRetired]

Try this formula in F2 and it should fill down automatically in the table.

Please Login or Register  to view this content.