-1

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.

3
  • 1
    What did you try so far?
    – CaldeiraG
    Feb 5, 2020 at 16:27
  • it is helpful to use proper nomenclature A.a and easily be confused for, say A:A use alpha!A1 if using formulas or Worksheets("alpha").Range("A1") if using VBA. That said, it looks like a COUNTIF should do what you want. Feb 5, 2020 at 16:31
  • I have tried vlookup, but I don't quite understand the column index input. I have also tried to make a logical expression, however I'm not sure it takes arrays, nor am I familiar with how to write compositional functions. Also the match function, but I'm not sure how I would make the match function scale down (drag down). It should also be mentioned that the inputs in A.a are from an importrange function.
    – Gwen Di
    Feb 5, 2020 at 16:32

1 Answer 1

0
=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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .