Forum home » Delegate support and help forum » Microsoft Excel Training and help » Vlookup

Vlookup

ResolvedVersion 2016

Tue 16 Jul 2019, 11:06 Delegate Lee said...

Lee has attended:
Excel Advanced course

Vlookup

When using vlookup and having the function across more than one column, is there a way of dragging the initial formula across the relevant cells and having the referenced column number increase accordingly?

ie. =VLOOKUP($A$5,$A$9:$F$33,2,0) in cell B5
=VLOOKUP($A$5,$A$9:$F$33,3,0) in cell C5
=VLOOKUP($A$5,$A$9:$F$33,4,0) in cell D5

I dragged the formula over from B5 to C5 and D5 but then had to change the 2 to 3 or 4 etc etc.

Thanks,

Lee

Tue 16 Jul 2019, 15:44 Trainer Dennis said...

RE: vlookup

Hi Lee

The short answer is NO. There is no quick easy way of autochanging the column number.

There is an alternative solution to VLOOKUP & HLOOKUP which is using INDEX & MATCH.

I've linked a web page which provides a good example of how they can be used to return data from a list.

https://superuser.com/questions/322001/how-do-i-prevent-vlookup-from-breaking-after-i-add-a-column-to-a-named-range

Hopefully, this will help.

Regards

Dennis

Excel tip:

Turn off AutoComplete in Excel

You may have noticed when typing into your spreadsheets that if you start to enter labels that begin with the same letters as a label that has been previously entered in the same spreadsheet, Excel will try and automatically complete the text for you. This feature is called AutoComplete.

If you find this feature more annoying than useful, you can turn it off by:

1. Going to Tools - Options.

2. Select the Edit tab.

3. Remove the tick from next to the "Enable AutoComplete for cell values" option.

4. Click OK.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Vlookup

Vlookup

RE: vlookup

Excel articles

Excel tip:

Turn off AutoComplete in Excel