I want to scan down column A in sheet alpha (alpha!A) which is a list of emails such that if, for each cell A1,A2,...AX, the said cell matches any of the entries in column D of sheet beta (beta!D), then it returns the horizontally corresponding entry in beta!E. This last part of the output coming from beta!E is of lesser importance, simply the position# of the match in beta!D would suffice. Edit: Also it must also return an empty cell if there is no match between alpha!A and beta!D. Thanks much.
1 Answer
=VLOOKUP(A1,BETA!D:E,2,FALSE)
The Column Index Number is asking you "how many columns to the right is the data you want?" If your matching data is in column D, then D is 1, E is 2, etc. So the formula can be interpreted as:
VLOOKUP(What are you looking for?, Where are you looking for it?, How far to the right is it located?, Do you want to use exact matches or approximate matches?)
Edit: This will return "#N/A" if the term you are searching for does not exist in your beta!D:E range. If you specifically need a blank cell returned in this case, then the simplest thing that I have found is just =IFERROR(VLOOKUP(yadda),""). You can modify this to return anything you like in the cases where VLOOKUP fails.
A.a
and easily be confused for, sayA:A
usealpha!A1
if using formulas orWorksheets("alpha").Range("A1")
if using VBA. That said, it looks like aCOUNTIF
should do what you want.