Multiply cells in two worksheets conidtion is matched and use of looping

[tsiddiq23]

Hello friends,
There are two worksheets in the attached workbook ("CDL" and "ID"). When there is a match between a cell in column A of "CDL" and a cell in column A of "ID", I want to multiply for the same row, column D of "ID" multiplied with column E of "CDL" till the end of data in "CDL". The multiplication result should be in column G of "CDL". Can you please help? Thank you.

Best regards,
Tahir

[JBeaucaire]

The basic formula in G4 would be:

=E4 * INDEX(ID!D:D, MATCH($A4, ID!$A:$A, 0))

...copy that cell downward.


If you don't want to see the Not Applicable (#N/A) results for the nonmatches, then:

=IFERROR(E4 * INDEX(ID!D:D, MATCH($A4, ID!$A:$A, 0)), 0)
or
=IFERROR(E4 * INDEX(ID!D:D, MATCH($A4, ID!$A:$A, 0)), "")

[tsiddiq23]

Thank you JBeaucaire. It works. You guys are genius in providing a simpler solution. I was thinking on the lines to have two loops (one in each worksheet) to go through row by row to match the cells till the end of data (last row) in each worksheet and get the multiplication result in the desired column. Can it be done that way as well? Can you guide in that way as well (if you can spare a few minutes of your time)? Thank you again.

[JBeaucaire]

I never do with VBA what a basic formula can do. Others may rise to the challenge, though.

Is there some non-obvious benefit to using VBA?