VLOOKUP in last column of Table_array

Question

I understand that VLOOKUP searches the first column of a table in order to find a value, then it grabs the value from the same row and a different user-specified column. The following code returns data from the 2nd column, column B.

VLOOKUP(5,$A$2:B100,2)

Is there a way to set the return column to the last column of the input table? Something like the following, which would return data from columns B, P, and AC, respectively.

VLOOKUP(5,$A$2:B100,end)
VLOOKUP(5,$A$2:P100,end)
VLOOKUP(5,$A$2:AC100,end)

Alternatively, is there a way to grab the current column number and use that as an index?

VLOOKUP(5,$A$2:B100,current_column_number)

I'd like to write one VLOOKUP formula and then be able to drag it right across the spreadsheet, so that B100 becomes C100, D100, E100, etc. and the column lookup changes accordingly.

Update

I can do the alternate approach using the COLUMN function, but it requires programming a fixed offset and doesn't seem as robust. I'd still like to know if there is an "end" option.

1. There is no end option. 2. As none of your VLOOKUP function examples uses the optional range_lookup parameter, I would remind you that without it, the first column must be sorted ascending for the function to work correctly as the default for range_lookup is TRUE. An exact match on unsorted data can be made by setting range_lookup to FALSE. — user4039065
– user4039065, Commented May 3, 2015 at 18:54

user3819867 · Accepted Answer · 2015-05-03 18:44:58Z

2

=VLOOKUP(5,$A$2:B100,COLUMNS($A$2:B100))

Unfortunately you cannot simply drag it, you'll need to replace as there are two equivalent ranges written in the nested function.
The COLUMNS effectively counts the columns in the range giving the exact result needed for the VLOOKUP's end variant.

EDIT to show OP what a simple drag function would be like:

Function VLOOKUP2(Expected As Variant, Target As Range)
x = Target.Columns.Count
VLOOKUP2 = Application.WorksheetFunction.VLookup(Expected, Target, x)
End Function

edited May 3, 2015 at 18:44

answered May 3, 2015 at 18:31

user3819867

1,1181 gold badge8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cody Martin Over a year ago

"Unfortunately you cannot simply drag it"? The COLUMNS function does exactly what I was looking for with end, and it looks like I can drag it.

user3819867 Over a year ago

Yeah, but on initial highlighting, you'll need to find the area twice.

Tim Biegeleisen · Accepted Answer · 2015-05-03 18:30:41Z

1

You can use the Excel COLUMN() function to convert the column reference to a numerical index into the VLOOKUP table. Try this:

VLOOKUP(5, $A$2:B100, COLUMN(B2))
VLOOKUP(5, $A$2:P100, COLUMN(P2)
VLOOKUP(5, $A$2:AC100, COLUMN(AC2))

In pratice, you can just enter the first formula I gave above and then copy to the right. Each copy will automatically shift the column number to the end.

answered May 3, 2015 at 18:30

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Daniel Puiu · Accepted Answer · 2018-04-14 02:04:02Z

1

You could use the count function while holding ($) one side of the count range, thus giving you an integer that Vlookup can use.

Something like:

VLOOKUP(5,$A$2:B100,COUNT($A$2:A2))

You may need to add a + or - 1 to the count function depending on where your range starts.

It's effectively doing the same thing you already did with the array for the vlookup

edited Apr 14, 2018 at 2:04

Daniel Puiu

9616 gold badges21 silver badges29 bronze badges

answered Apr 13, 2018 at 19:32

Jake Winters

416 bronze badges

Collectives™ on Stack Overflow

VLOOKUP in last column of Table_array

Update

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related