Can you tell me what's going on here?

J

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)
 
D

David Biddulph

ROW(18:18) returns 18
ROW($4:$4) returns 4

The ROW function is covered by Excel help, as are all other Excel functions
except DATEDIF.
 
J

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.
 
Top