If column A and B have a match then multiply column C with D

[Bob_ipc]

Hey guys I seem to be stumped!

I have a spreadsheet and I am trying to get a value in an individual cell based on two rows of data on two different tabs and then multiply by another row of data

Sheet 1 has
column A, 63 lines with numbers
column B, 63 lines with numbers

Sheet 2 has
column A has 1200 lines with numbers
Column B has 1200 lines with numbers
Column C should have result

anytime column A in sheet 1 matches the value in column A in sheet two, then multiply the cell in Column B in sheet 2 with Column B in sheet 1 with the result in Sheet 2 column C

I could do 63 nested if statements but that just seems like I would be looking for trouble, is there an array formula that would get me the results I am looking for?

Any direction would be appreciated!

Thanks in advance

 

[jtakw]

Hi,

If I understand correctly, try this in Sheet 2, Column C

Change/adjust cell references/range accordingly:

=IFERROR(INDEX(Sheet1!B$1:B$63,MATCH(A1,Sheet1!A$1:A$63,))*B1,"")

 

[Bob_ipc]

Dude! that worked perfectly!! So Awesome!

Thank you!!

 

[jtakw]

Dude! that worked perfectly!! So Awesome!

Thank you!!

You're welcome.

If that worked for you, here's a shorter formula using VLOOKUP:

=IFERROR(VLOOKUP(A1,Sheet1!A$1:B$63,2,0)*B1,"")

Nothing wrong with the INDEX/MATCH I provided above, just another option.