VLOOKUP and best-right way

I use the VLOOKUP with 2 ways: 1) =IFNA(VLOOKUP(A2:A1344,Sheet2.J$2:K$66,2,0),"") 2) =IFNA(VLOOKUP($A2,Sheet2.$J$1:$K$66,2,0),"") and i take same results who is better way ? its same or i miss something ? can explain me the$ before column letter ($J...) and the$ after column letter($J$1)?

edit retag close merge delete

Sort by » oldest newest most voted

The first argument for VLOOKUP is the Search Criterion, i.e., which value should it lookup in the following range. The value is scalar, single value. So, there's usually no reason to use range here (as in your #1). If you do, then VLOOKUP still will use a single value from the range provided as first argument, which one is decided "by intersection": it will try to get value from the range which is either in the same row, or in the same column. I.e., if your formula #1 is placed into, say, B2, then VLOOKUP will take for its first argument from A2, which belongs to the range A2:A1344, and is in the same row. If you put the same formula to B3, then VLOOKUP will use A3 as its 1st argument.

The $ before parts of address (there are three such parts: sheet name, column, and row) defines that this part of address is absolute-referenced. When there's no $ before a part of address, then that part is considered to be relative-referenced. I.e., if you put a formula like =A1 into A2, this means to Calc that you want to put into this cell a value of (the same sheet - by default, omitted), the same column, and one row above. If you later copy the cell and paste to another cell, like C12, that cell will contain the same relative reference - i.e., the same sheet, the same column (C), one row above (i.e., 11).

But if you modify the formula in A2 like this: =$A1, this will mean: I want value from column A - no matter what; however, the row is still relative - one above. Copied and pasted into C12, the formula will become =$A11: the absolute-referenced part remains unchanged, relative-referenced part changed relative to new place.

So, there's not "$after column letter", there's "$ before row number".

All this might be irrelevant if you only write a formula to a single cell. But if you then copy it to multiple cells, it will become a big deal. If you choose relative/absolute references wisely, then you will have no problems with copying - you will not have to correct all the references later, they will be correct right away. But if you fail to properly define which references are which type, then you will end up with the need to adjust every single cell where you copy your formula.

more