Ask Your Question

VLOOKUP and best-right way

asked 2017-09-13 22:35:06 +0200

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-09-13 23:03:42 +0200

Mike Kaganski gravatar image

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.

edit flag offensive delete link more


ok !!! thank you my friend

dimzev gravatar imagedimzev ( 2017-09-14 10:59:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-13 22:35:06 +0200

Seen: 25 times

Last updated: Sep 13