Map/Copy values from one sheet to another in excel if they match on column header

This post gets me very very close to where I need to be

How can I copy a column from one sheet to another sheet based on title in Excel?

However if I add an =if(isblank(Sheet1!A2),””,… to the beginning of the formula, the first column always shows as blank.

I only need to copy the values if there is actually a value in the cell

If I use the original formula, if the cell is blank, it returns a zero or “#N/A”

In fact, it still does that for some things even if i include the “if” statement. I think it’s because the source data sometimes is “blank” meaning there’s like a space or line break character in it, but that’s something I might just have to deal with

The main issue is that first column being empty even though there is matching data in the source sheet

Link to my file: https://drive.google.com/open?id=1IZ1BpisMhHcSKJuBRLWnTU4RxpeZ2lCZ

The formula in column A on the test sheet, first checks if column A on Sheet1 is blank. There is no lookup to match headings in this test. The formula in Test!A2 first asks if Sheet1!A2 is blank? Looking for the proper column heading doesn’t occur in the first IF.

If you want to populate “Test” with all of the values of a row on Sheet1 when the row on Sheet1 has a non blank last name:

=IF(ISBLANK(HLOOKUP($B$1,Sheet1!$A:$ZZ,ROW(),FALSE)),"",HLOOKUP(C$1,Sheet1!$A:$ZZ,ROW(),FALSE))

(Column C formula)

This test says:

• HLOOKUP($B$1,Sheet1!$A:$ZZ,
  • always (absolute reference $B$1) look for this Test sheets Column B Header in the header’s list from Sheet1.
  • The $B$1 in the formula can be replaced with “Last Name” (and probably should).
• ROW(),FALSE
  • The “false” says find an exact match. If found then return a value from Sheet1 in the column where found (HLOOKUP). The value in the column at a certain row which is specified by row() which is the row on the test sheet of this formula (so the same row on Sheet1).
• IF(ISBLANK(HLOOKUP
  • If the result found by hookup ISBLANK then the value of this formula cell is “”
  • If ISBLANK returns false because the Last Name Column on Sheet1 at this formulas row had text, perform the false argument.
• If test, true (“”), false = HLOOKUP(C$1,Sheet1!$A:$ZZ
  • Same `HLOOKUP as before, but instead of always looking at the absolute reference to column B, the reference is relative/dynamic.
  • Non absolute references change when the formula is copy dragged. So the “C” of C$1 will change to “D” when copied to the next column right (or B if copied left). Again, this is because there is no “$” locking down the reference as absolute. Since the column letter is the same as the column of the formula, the formulas column header is searched among the headers on Sheet1.
• The rest of the logic is the same as the first HLOOKUP

Alright, here’s what I ended up with:

=IFERROR(IF(HLOOKUP(A$1,Sheet1!$A:$ZZ,ROW(),FALSE)="","",HLOOKUP(A$1,Sheet1!$A:$ZZ,ROW(),FALSE)),"")

The IFERROR is to get rid of all of the “#N/A” returns

The IF is to make the return actually blank if the source is blank, rather than just a Zero “0”

The issue with my original formula was that the “IF” statement was running before the “HOOKUP”, so for that first column it was returning its own blank answer

Thank you to @Ted D (https://superuser.com/users/994103/ted-d) for your help understanding how the order of my original formula was being handled logically by the program, and how HLOOKUP works so I could find the rest of this to add to it