I have 2 lists of data.

List A contains 2 columns - Lead ID (column A) & Email ID (column B). This list contains 3,175 rows.

List B contains 2 columns - Email ID (column A) & Email Address (column B). This list only contains 3,085 rows.

I need to match up the Lead ID with the Email Addresses based on the similar values of the Email ID.

It was almost perfectly easy until there are 90 rows missing from the second list and I dont know how to sift out that rows.

Any ideas?

ScottGem

Use a VLookup()

JBeaucaire

Let's assume List A is on Sheet1. List B is on Sheet2.

On Sheet1 in column C, starting at C2 try this:

=IF(ISNUMBER(MATCH(B2, Sheet2!A:A, 0)), INDEX(Sheet2!B:B, MATCH(B2, Sheet2!A:A, 0)), "No Match")

VLOOKUP does work, too, and might be easier to understand, but INDEX/MATCH is more robust and may perform better over huge datasets.

DrJ

I dont know how to use vlookup yet.

the ID #s are alphanumeric... could that be why the 2nd solution doesnt seem to be working?

DrJ

Trying vlookup... I have it set up like this:

|_____Sheet1_______|
|___A____|_____B___|
|_Lead ID_|_Email ID_|



|__________________________Sheet2_________________ _______________|
|___A____|_____B_____|___________________C________ _______________|
|_Email ID_|___Email___|_=VLOOKUP(A2,Sheet1!$B$2:$B$3157,S heet1!A2)__|




I was hoping Sheet2 Column C would return the lead ID from Sheet1 but its returning a #REF!

JBeaucaire

Ok, you indicated your need backwards to me, and you can't use VLOOKUP at all to do this, and my formula just needs adjusting to be on the correct sheet.

On Sheet2 C2:

=IF(ISNUMBER(MATCH(A2, Sheet1!B:B, 0)), INDEX(Sheet1!A:A, MATCH(A2, Sheet1!B:B, 0)), "No Match")

Don't just paste in the formula and use it once it's working. Be sure to analyze each piece and understand what it's doing. INDEX/MATCH is the most robust function, in my opinion, and it's uses are FAR and WIDE.

DrJ

Awesome! That seems to have worked perfectly.

I love working in Excel and always want to know how things are working or how to use new formulas. I am not familiar with INDEX or MATCH so I will definitely be figuring them out.

Thank you very much

JBeaucaire

VLOOKUP is the tool of choice for most people at first. It requires several partner tools, LOOKUP and HLOOKUP. It's simple to use once you understand it.

The problems are:
1) It only does lookups to the RIGHT. This means you have rows of data, you must search in the first column and return a value from the columns to the right...ONLY.

You weren' t using it right, but this is what a normal VLOOKUP looks like:

=VLOOKUP(A2, Sheet2!A2:K100, 11, 0)

So, that formula find the value in A2 over on sheet2 in column A, then returned the value in the 11th column of the data range you described.

See, you can only look to the RIGHT.

2) It's a memory hog. If you have a huge dataset like the one in the example above, Excel reads into the memory the ENTIRE data set from A2:K100 to find the value wanted over in column K. What a waste.

3) It can't be used to deal with data in any other direction. can't go left, and if you want to look for data in horizontal datasets, you have to switch to HLOOKUP. Ugh.

NOTE: With HLOOKUP, you similarly can only look DOWN, you can't look up for your wanted values.
NOTE: You can use LOOKUP to look in either direction, but your data HAS to be sorted.

===========
Now, with INDEX/MATCH, you can look in any direction...up,down,left,right with the same function, and you only have to read in the data needed.

Once you wrap your head around the INDEX/MATCH approach, you'll stop using VLOOKUPs altogether, I promise.