SOLVED

# If Cell on Sheet 1 & 2 match, copy cell on Sheet 2 to a new cell on Sheet 1

Copper Contributor

# If Cell on Sheet 1 & 2 match, copy cell on Sheet 2 to a new cell on Sheet 1

Sheet 1 has a list of company payments. Sheet 2 has a list of companies and their main contacts.

I am looking for a formula for the following statement:

If the company name in column A on sheet 1 matches a company name in column A on sheet two, then the email (column D) for that company's contact on sheet 2 will be copied to column I on sheet.

Example Workbook:

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: If Cell on Sheet 1 & 2 match, copy cell on Sheet 2 to a new cell on Sheet 1

``=INDEX(Sheet2!\$D\$2:\$D\$13,MATCH(Sheet1!A3,Sheet2!\$A\$2:\$A\$13,0))``

You can try INDEX and MATCH.

Sheet1:

Sheet2:

# Re: If Cell on Sheet 1 & 2 match, copy cell on Sheet 2 to a new cell on Sheet 1

If you have Microsoft 365 or Office 2021, enter the following formula in I3:

=XLOOKUP(A3:A10,Sheet2!A:A,Sheet2!D:D,"")

This will automatically spill to the rows below.

If you have an older version, use

=IFERROR(VLOOKUP(A3,Sheet2!A:D,4,FALSE),"")

and fill down to I10.

# Re: If Cell on Sheet 1 & 2 match, copy cell on Sheet 2 to a new cell on Sheet 1

This worked! Thank you!