PDA

View Full Version : Solved: VLOOKUP?s 3rd Arg ? Why Needed??



Cyberdude
12-31-2005, 10:49 AM
I have always been puzzled that the function VLOOKUP requires me to specify it?s third argument (the relative column number) when it seems to have the information it needs in the second argument. Said differently, when writing the VLOOKUP function, why would you specify an array in the second argument (say, A1:10) then specify the third argument as, say, 3, to tell VLOOKUP to return a value from column ?C?. VLOOKUP would have what it needs if you just specified the array as A1:C10.

The third argument seems at first glance to be superfluous. So why would you ever need it unless you wanted to make it a variable perhaps? I suppose you could write the function in a statement something like:

=VLOOKUP(?Mary?,A1:10,G1)

where G1 contains the relative column number within the array. Is that why the third arg exists? The problem is that when I tested this format, it gave an error. Apparently VLOOKUP insists that the third arg be a number, not a cell containing a number. Curious implementation.
Hey, Happy New Year everybody!! http://vbaexpress.com/forum/images/smilies/bigdance2.gif

Zack Barresse
12-31-2005, 11:10 AM
It can be a cell containing a number, just so long as the cell actually contains a number.

As far as the third argument, I generally make it a variable most times I use the formula. I don't know what I'd do without it! LOL!

I think we can just chalk this up to the quirks of the development team.

Bob Phillips
12-31-2005, 11:33 AM
I have always been puzzled that the function VLOOKUP requires me to specify it?s third argument (the relative column number) when it seems to have the information it needs in the second argument. Said differently, when writing the VLOOKUP function, why would you specify an array in the second argument (say, A1:10) then specify the third argument as, say, 3, to tell VLOOKUP to return a value from column ?C?. VLOOKUP would have what it needs if you just specified the array as A1:C10.

The third argument seems at first glance to be superfluous. So why would you ever need it unless you wanted to make it a variable perhaps? I suppose you could write the function in a statement something like:

=VLOOKUP(?Mary?,A1:10,G1)

where G1 contains the relative column number within the array. Is that why the third arg exists? The problem is that when I tested this format, it gave an error. Apparently VLOOKUP insists that the third arg be a number, not a cell containing a number. Curious implementation.
Hey, Happy New Year everybody!! http://vbaexpress.com/forum/images/smilies/bigdance2.gif

No, it is because you may not want the final column oin the 2nd argument. For instance, you may want the value from the B column in table A1:C10, not necessarily C. If it always assumed the last column of the 2nd argument, if getting first column B, then C, then D, would require having a different formula each time, ratyher than the same formula with a different third argumenty. It makes it more flexible, and also allows for dynamic column allocation.

Jacob Hilderbrand
12-31-2005, 11:41 AM
Basically it just gives you more options. I know that I have had named ranges that spanned several columns and used vlookup to return a value from one of the columns (which was not necessarily the last column).

byundt
12-31-2005, 12:33 PM
If you have a double lookup to do, then you will need a variable in the third column. For example, suppose you want to find sales for a certain customer during a specified month. If the lookup table has each month in a separate column, and each customer in a separate row, then VLOOKUP will return the right row, and MATCH will identify the right column.

Brad

Cyberdude
12-31-2005, 04:02 PM
Thanx, gents. I started staring at a VLOOKUP I had and for some odd reason starting wondering about that. Now I know. I guess I just never ran into a situation where I could make use of arg 3 in the manner(s) you have described. Always learning. http://vbaexpress.com/forum/images/smilies/friendship.gif