Comparing columns in two different excel sheets and if they match copying third column
I have limited working knowledge of Excel and I hope someone will be able to help me with my problem.
I have two different work sheets (say F1 and F2) with last name in Column A, first name in Column B. The email address are present in Column F of F2. I need to compare the names and if the names match I need to copy the corresponding email address in to F1.
Thank you
If I understand your question correctly, you would like a formula that will read the first and last name on F1, locate the corresponding row on F2, and return the email address. This array formula will accomplish this. Make sure you press Ctrl+Shift+Enter when entering the formula because it is an array formula.
=INDEX('F2'!F:F, MATCH(A1&B1, 'F2'!A:A&'F2'!B:B, 0))
This assumes the following:
- Names are located on columns A and B on F1 and F2
- Email addresses are located in column F on F2
- Formula is entered into sheet F1
Note that the row numbers should be changed depending on where you enter the formula (the 1's in "A1&B1".
I had a similar issue, and used bdr9's answer to come up with my own. If I had a better hit on my google searches I would post this there, but this stack exchange question is the closest I got.
I was trying to match one value in a range of values and return an associated value from a different column of the matched value. I was comparing MAC addresses (for IP phones) from a list I had off one server to one that had the MAC addresses with the assigned names.
I wanted a shortcut to matching MACs with names, and the unknown name list had more/different MACs than the the list that had the names. This is what I came up with.
column E had the MACs that associate with the known names of column B. Column F had the unknown MAC list. My formula was input on column G, in line with the unknown MAC list. “$” is used to set the ranges absolutely.
as input on G2
=INDEX($A$3:$A$112,MATCH(E2,$D$3:$D$112,0))
=INDEX([value you wish to return when there is a match],MATCH([cell you are trying to match],[range you are matching to],[not sure what the 0 is for]))