VLOOKUP() function only returns values from the columns to the left of the key field. You can’t build the formula and give it a column index number of –1, for instance. This is will result on a #VALUE error, like on the following example:
On this example, we want to retrieve the value on the Date column that matches with the ID on cell B14 so we putted a –1 column index number on the function that didn’t worked.
A simple way of going around this problem is to build our own VLOOKUPNEG (as from negative) function that works with negative column index numbers and can return values to the left of the key field, in this case, the Date value that is on the left of the ID column. So, this is the UDF (User Defined Function) that you can create:
Function VLOOKUPNEG(lookup_value, table_array As Range, col_index_num As Integer, CloseMatch As Boolean)
Dim RowNr As Long
RowNr = Application.WorksheetFunction.Match(lookup_value, table_array.Resize(, 1), CloseMatch)
VLOOKUPNEG = table_array(RowNr, 1).Offset(0, col_index_num)
End Function
Using this UDF, on the same example, we will get this result:
Now, on cell B15, we are already getting the Date that matches the 1002 ID that was on cell B14: 14-05-11!
11 comentários:
great post. Amazed how often people need a vlookup that goes left and not right. keep up the good work
Thanks for your kind words. This is the kind of comments that makes me continue posting here...
Best regards,
jppinto
It appears I don't have the function VLOOKUPNEG in excel 2010? Are anyone able to explain this?
Excel 2010 (or any other version of Excel!) don't have a VLOOKUPNEG() function! This is what's called a UDF (User Defined Function) that is writen in VBA so that it can be available on you sheet.
Please read all of the article carefully to understand how to use this function.
got to see this blog today, but you can always use combination of INDEX and MATCH function which performs better than vlookup or vlookupneg in this case
Many thanks, very useful.
Again, as others have mentioned, very helpful
As harishbagayat mentioned, another way to do this is INDEX and MATCH. (It works great as a VLookup replacement as well). This is also much simpler to use than the UDF, since you don't have to create any custom functions.
In the example you give, you can also return the date by entering this formula in B15:
=index(A2:A12,match(B14,b2:B12,0))
But the udf is so much easier to use within vba code.
Many Thanks for the udf.
The formula give me a #name error why is that?
This doesn't seem to work. I also receive a #NAME? error.
Post a Comment