In Excel 2011 on Mac, how to return a non-blank matching value with vlookup?

Question

I have a list of domain names and their registrars on one sheet, and the same list of domains and other data (but not registrars) on another sheet.

I need to get the registrar name from sheet A, to be next to the corresponding domain name in sheet B.

CSV example of sheet A:

domain1.com,,9.98,03/24/13
domain1.com,,9.98,03/24/13
domain1.com,godaddy,9.98,03/24/13
domain2.com,,9.98,03/24/13
domain2.com,godaddy,9.98,03/24/13
domain3.com,namecheap,9.98,03/24/13

CSV example of sheet B:

domain1.com,1200,04/01/14
domain2.com,402,04/01/14
domain3.com,612,04/01/14

I desire to have the registrar in a 4th column of sheet B for each domain.

The problem is that on sheet A, there are multiple instances of the domain where it was with other registrars in the past, but those cells have been erased. This causes my vlookup from sheet B, to find the first match of the domain name and return the corresponding registrar, which ends up being blank (returns zero).

Is there a way to get a VLookup (or other formula) to return only a corresponding registrar that is NOT blank?

I've experimented a bit with Index & Match but wasn't able to get any closer.

Answer 1

You can use an array formula to do this look up. Where your original data is in A1:D6, you can use this formula (adjust sheet references to match your workbook):

=INDEX(SheetA!$B$1:$B$6,MIN(IF(SheetA!$A$1:$A$6=SheetB!A1,IF(SheetA!$B$1:$B$6="",2000000,ROW(SheetA!$B$1:$B$6)),2000000)))

Paste this into the formula bar and press Ctrl+Shift+Enter. Then fill down.

Explanation and assumptions:

• The IF statements inside the MIN function form an array of numeric values. For records that meet the criteria (i.e., domain name matches and registrar is not blank) the row number of the record is stored in the array. For records that do not meet the criteria, the number 2000000 is stored in the array.
• 2000000 is an arbitrary number greater than the number of rows in an Excel sheet. This is just to guarantee if no matches are found, the INDEX function will return an error.
• INDEX returns the value at a specified position in an array.
• The row number of the record is being used as a stand-in for the records position in the table. This is fine if the table begins in row 1 because record 1 is in row 1. However, if your data begins in a different row, you will need to tweak the formula to make this work. For instance, if record 1 is in row 2, you will need to substitute ROW($B$2:$B$7) with ROW($B$2:$B$7)-1. Otherwise, the INDEX function will return the wrong registrar.