Negative VLOOKUP()–return the value to the left of the key field

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:

Tips For Excel said...

great post. Amazed how often people need a vlookup that goes left and not right. keep up the good work

jppinto said...

Thanks for your kind words. This is the kind of comments that makes me continue posting here...

Best regards,


Anonymous said...

It appears I don't have the function VLOOKUPNEG in excel 2010? Are anyone able to explain this?

jppinto said...

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.

harishbagayat said...

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

vicsar said...

Many thanks, very useful.

Anonymous said...

Again, as others have mentioned, very helpful

Anonymous said...

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:


Anonymous said...

But the udf is so much easier to use within vba code.
Many Thanks for the udf.

Anonymous said...

The formula give me a #name error why is that?

Anonymous said...

This doesn't seem to work. I also receive a #NAME? error.