vlookup entire column

[rplim2016]

Good Day Mr. Excel users,

I saw my colleagues doing this weird formula that I never seen.

=VLOOKUP(E:E,reference!I:L,4,0)

above lookup value refers to entire column E:E but assuming above formula in in row 2, it only uses E2 as reference and it works.


Can someone please explain to me on why he's doing that instead of this? =VLOOKUP(E2,reference!I:L,4,0)

Thanks!

 

[Canapone]

Hi

a brave attempt.

Try to move the two VLOOKUPs some row below with cut and paste:

=VLOOKUP(E2,reference!I:L,4,0) will return the correct result

=VLOOKUP(E:E,reference!I:L,4,0) will not return the expected result.

I'm an Excel user ( not a Guru), maybe if VLOOKUP has to read an array as its first argument, it returns a result according to the context/ the position of the formula.

Like the formulas you work with in tables.