Can you tell me what's going on here?

[J.Mart]

Hi,
I'm studying a sheet that someone else has made with the prospect of
creating a new sheet in the same format but with different data. Can anyone
explain to me what is going on in these vlookups, specifically the ROW
references? How does this work? Thanks!

=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(18:18)-ROW($4:$4),FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(31:31)-ROW($4:$4)+1,FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(62:62)-ROW($4:$4)-2,FALSE)

 

[JE McGimpsey]

ROW($4:$4) just returns 4, so the equivalent would be:

=VLOOKUP(F$1,L.1_d!$1:$65536,14,FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,28,FALSE)
=VLOOKUP(F$1,L.1_d!$1:$65536,56,FALSE)

The only difference will be that if a row is inserted in sheet L.1_d,
the cells with the ROW() functions will adjust - so if a row was
inserted at row 35, the first two functions will remain the same, but
the last would become

=VLOOKUP(F$1,L.1_d!$1:$65536,ROW(63:63)-ROW($4:$4)-2,FALSE)

That said, with the info you've provided, it's impossible to tell if
that would be logical, or whether it's just arcane and prone to error.

 

[Teethless mama]

Replace: ROW(18:18)-ROW($4:$4)
with: 14*2^(ROWS($1:1)-1)
and copy down