Look up a value in another sheets column and add value from that row to it

Question

So here is what I like to do. I would like to look up if these email addresses (Image 1) are present in column C from another sheet (Image 2). If it is present I would like to have the "Name" and "Address" from that sheet to be placed in column B and C in the first sheet.

What formula should I use here?

Image 1

Image 2

Answer 1

If you have newer version of Excel (Excel 365 or Excel 2021), you can use the function XLOOKUP. Link to Microsoft Page about the XLOOKUP

The formula in your case would look like this:

=XLOOKUP(A2,Sheet2!C:C,Sheet2!A:A,"not found")

You give it value you search ("A2"), in which column it will search (C:C on Sheet2) and from which column it will return the results (A:A Sheet2). The "not found" part is a custom string I set up for cases when searched value is not found in the list)