1

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.

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.
  • This looks close... but I'm probably not understanding how to change the formula to work with my data. The data is on two different sheets, so assuming in your example A is for sheet A and B is for sheet B, I ended up with this formula which is returning something, but not the correct data and I can't really even figure out what it's returning. =INDEX(B:B,MIN(IF('domains'!A:A=B14,IF(B:B="",2000000,ROW(B:B)),2000000))) Also, where is this returning the row next to the matching value? I'm not seeing where that happens. – Jonathan van Clute Apr 7 '14 at 19:48
  • Sorry, that was confusing. I'll edit sheet references into my formula. – Excellll Apr 7 '14 at 20:49
  • I must still be screwing something up. This time I ended up with =INDEX('domains'!A:A,MIN(IF('domains'!A:A=B2,IF('domains'!A:A="",2000000,ROW('domains'!B:B)),2000000))) but I get a #REF error. This formula btw is supposed to go in sheet B yes? – Jonathan van Clute Apr 7 '14 at 21:08
  • Disregard previous comment, I had forgotten to make it an Array formula. Now it is returning the same incorrect information as before, basically nothing has changed. – Jonathan van Clute Apr 7 '14 at 21:09
  • OK I think I got it... had a typo. Will verify if this is working as I need it to and accept the answer if so. Thanks! – Jonathan van Clute Apr 7 '14 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.