google sheets - Compare two tab's headers, copy column below maintaining blank columns

Question

I have two tabs, one with all of my column headers (data tab), the other tab with less headers, but all from the data tab.

I want to search the headers of the "data" tab using the headers from the second tab starting at column I through BI. The second tab's headers will periodically change but always be present in the data tab. If the search criteria isn't present, I want to leave that column blank. If it is present, return the values below from the data tab.

I've tried a few formulas, but can't quite get what I'm looking for.

This formula worked to find the data:

'FILTERED'!I2

=FILTER(data!I2:AK,COUNTIFS($I$1:$1,data!I1:AK1))

This formula got the placement right, but produced the wrong information:

'Copy of FILTERED'!I3

=ArrayFormula(IF(ISBLANK(I2:2),,FILTER(data!I2:AK,COUNTIFS($I$1:$1,data!I1:AK1))))

Here's my sheet.

question from:https://stackoverflow.com/questions/65660861/compare-two-tabs-headers-copy-column-below-maintaining-blank-columns

Answer 1

You've got a large range to process, so there may be a slight delay filling the grid by formula (2 or 3 seconds maybe). But I have added a sheet ("Erik Help") with the following formula in I2:

=ArrayFormula(IF(ROW(A2:A),IFERROR(VLOOKUP(ROW(data!A2:A),{ROW(data!A2:A),INDIRECT("data!I2:"&ROWS(data!A:A))},HLOOKUP(FILTER(I1:1,I1:1<>""),{data!I1:1;SEQUENCE(1,COLUMNS(data!I1:1),2)},2,FALSE),FALSE))))

Honestly, it's hard to explain how this works, but I'll try to cover the basics.

=ArrayFormula(...)

This just means the formula will be processing a range rather than one cell.

IF(ROW(A2:A), ... HLOOKUP(FILTER(I1:1,I1:1<>""),{data!I1:1;SEQUENCE(1,COLUMNS(data!I1:1),2)},2,FALSE) ...)

IF(ROW(A2:A) is important, because it signals to do something for every row, thereby creating a 2D grid instead of just processing the current row. The HLOOKUP will look up every header in I1:1 that isn't blank [FILTER(I1:1,I1:1<>"")], which as the sheet is now, will be all of them. They will be looked up in in a virtual array formed from a top row consisting of all headers in data!I1:1 over a bottom row made up of a SEQUENCE of numbers made of 1 row and the same number of columns as are in data!I1:1, starting at the number 2 and moving up. (It starts at 2, because part of the VLOOKUP virtual array which I haven't explained yet, will be forming a column 1.)

VLOOKUP(ROW(A2:A),{ROW(data!A2:A),INDIRECT("data!I2:"&ROWS(data!A:A))}, *the HLOOKUP RESULT NUMBER*,FALSE)

Now a VLOOKUP will kick in. It will look up every row in data!A2:A within a virtual array made of two columns; the first column will be those same row numbers, and the second will be everything from data!I2 over and down (the INDIRECT setup allows this to be a dynamic grid in case you add or delete columns later). As to which column from that should be returns, that will pull from the HLOOKUP results explained above (which, as you'll recall, will all match the headers).

Finally, IFERROR(...) will return null if any step in that process returns an error, which would be because something wasn't found.