Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-06-2017, 07:21 PM
kimak kimak is offline Need help with vlookup Windows 10 Need help with vlookup Office 2016
Novice
Need help with vlookup
 
Join Date: Feb 2017
Posts: 8
kimak is on a distinguished road
Default Need help with vlookup


I have two workbook and i managed to perform vlookup on both of them. However, i have issues with the formula not working when new record is added on both workbook. I tried using dynamic name range, offset and it works but my lecturer said that my formula was wrong and told that I only needed vlookup function. I was like surprised so I tried to google search but no dice. Is there a way to make vlookup works when new record is added without using any function other than vlookup? Thx.
Reply With Quote
  #2  
Old 03-06-2017, 08:26 PM
jeffreybrown jeffreybrown is offline Need help with vlookup Windows Vista Need help with vlookup Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Well let's say your Vlookup is =VLOOKUP(A1,$D$1:$E$8,2,0)

If you add a record to row 9, the VLOOKUP would fail because it is not a dynamic range.

So instead, reference the entire column =VLOOKUP(A1,$D:$E,2,0)
Reply With Quote
  #3  
Old 03-07-2017, 12:34 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need help with vlookup Windows 7 64bit Need help with vlookup Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Quote:
Originally Posted by kimak View Post
I tried using dynamic name range, offset and it works but my lecturer said that my formula was wrong and told that I only needed vlookup function.
It is indeed correct that you only need VLOOKUP ( although the INDEX/MATCH combination is much better IMO) using a dynamic range or a table as data source, so what's wrong with that?
You VLOOKUP would look something like =VLOOKUP("ab";Table1[#All];2;0) with a table

And I also think that using entire columns is not the best way to go as it forbids you to use those columns for other things than extending your data source
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 03-07-2017, 01:08 AM
kimak kimak is offline Need help with vlookup Windows 10 Need help with vlookup Office 2016
Novice
Need help with vlookup
 
Join Date: Feb 2017
Posts: 8
kimak is on a distinguished road
Default

Quote:
Originally Posted by jeffreybrown View Post
Well let's say your Vlookup is =VLOOKUP(A1,$D$1:$E$8,2,0)

If you add a record to row 9, the VLOOKUP would fail because it is not a dynamic range.

So instead, reference the entire column =VLOOKUP(A1,$D:$E,2,0)
Thx man, I followed your advice and I managed to get my formula work with new record inserted. Hopefully my lecturer satisfied with this approach.


Quote:
Originally Posted by Pecoflyer
It is indeed correct that you only need VLOOKUP ( although the INDEX/MATCH combination is much better IMO) using a dynamic range or a table as data source, so what's wrong with that?
You VLOOKUP would look something like =VLOOKUP("ab";Table1[#All];2;0) with a table

And I also think that using entire columns is not the best way to go as it forbids you to use those columns for other things than extending your data source
I tried many ways on making my vlookup formula dynamically and all of them work but my lecturer rejected them, saying that you only needed vlookup lol. I tried jeffreybrown's solution and it works but don't know whether my lect accept it or not. If not, then I'l use your solution and if he still rejected it then i dont know what else lol
Reply With Quote
  #5  
Old 03-07-2017, 05:33 AM
jeffreybrown jeffreybrown is offline Need help with vlookup Windows Vista Need help with vlookup Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

You're very welcome.

I agree, the entire column reference might be overkill, so instead of $D:$E you could extend to a reasonable length which would catch the new adds.

Maybe $D$1:$E$200 would suffice
Reply With Quote
  #6  
Old 03-07-2017, 10:44 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need help with vlookup Windows 7 64bit Need help with vlookup Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

I'm sure Jeff's solution will be OK'd
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 03-07-2017, 05:33 PM
kimak kimak is offline Need help with vlookup Windows 10 Need help with vlookup Office 2016
Novice
Need help with vlookup
 
Join Date: Feb 2017
Posts: 8
kimak is on a distinguished road
Default

Thx guys for all your help. My lecturer finally satisfied with my formula lol.
Reply With Quote
  #8  
Old 03-07-2017, 05:35 PM
jeffreybrown jeffreybrown is offline Need help with vlookup Windows Vista Need help with vlookup Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Great news

What solution did you offer that worked?
Reply With Quote
  #9  
Old 03-07-2017, 07:09 PM
kimak kimak is offline Need help with vlookup Windows 10 Need help with vlookup Office 2016
Novice
Need help with vlookup
 
Join Date: Feb 2017
Posts: 8
kimak is on a distinguished road
Default

Quote:
Originally Posted by jeffreybrown View Post
Great news

What solution did you offer that worked?
I used your suggestion and changed the table array so that it referred to the whole column.
Reply With Quote
  #10  
Old 03-07-2017, 07:11 PM
jeffreybrown jeffreybrown is offline Need help with vlookup Windows Vista Need help with vlookup Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Good job...

For your next step/solution...

http://www.contextures.com/xlNames01.html
Reply With Quote
  #11  
Old 03-07-2017, 11:40 PM
kimak kimak is offline Need help with vlookup Windows 10 Need help with vlookup Office 2016
Novice
Need help with vlookup
 
Join Date: Feb 2017
Posts: 8
kimak is on a distinguished road
Default

Quote:
Originally Posted by jeffreybrown View Post
Good job...

For your next step/solution...

http://www.contextures.com/xlNames01.html
Thx, i appreciate your help!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with vlookup Help using If and Vlookup heastlund Excel 3 08-04-2014 01:34 PM
Need help with vlookup IF and VLOOKUP SBMC Excel 1 11-07-2012 10:24 AM
Need help with vlookup Using IF and VLOOKUP together Becki Excel 2 04-06-2012 05:32 PM
Vlookup ibrahimaa Excel 8 01-03-2012 09:32 PM
Vlookup Karen615 Excel 4 09-12-2011 02:30 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:09 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft