vlookup
Train Strikes - 7th, 8th & 9th May 2024
Hide
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

  • Home
  • Courses
  • Promotions
  • Schedule
  • Formats
  • Our Clients

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

Vlookup

resolvedResolved · Low Priority · Version 2016

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






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 articles

Please browse our web site to find out more about
excel training class and other training courses.

Excel tip:

Converting an American date format to European using Formula

Excel depending on your local setting will only pick up date values of the dd mmm yyyy oders as date type. If you import data from various sources including America their date order is different with data value in mmm dd yyyy, excel can only treat it as text indicated by left aligning it. To overcome this you have to do the the following.

1. Extract the date components mmm dd yyyy, by using the the Text functions LEFT, MID or RIGHT

2. Reorder dd mmm component and concatenate using "&" in the right order this will create a text string with the date in the right order it then needss to be converted to a value so excel can recognise it.

3. To convert to value encase in TEXT function.

4. Format to desired date format.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.05 secs.