2.18 - VBA Vlookup in Another Sheet
HTML-код
- Опубликовано: 8 июл 2024
- This video was part of a full Excel VBA course "Excel VBA Exercises and Real-World Projects" which was initially uploaded on Udemy in 2018/2019.
I have since decided to upload the course on RUclips so everyone can watch the content for free.
How dare you create a simple video that’s straight to the point? I thought you were supposed to make it last 50 mins long but nope you aimed and achieved perfection. Thank you sir!! Wish I could give you more likes and subscriptions.
Thank you, my favorite comment yet!
Thanks Karen..!! Watched hundreds of videos in you tube on this topic. But yours was the the best.
Huge help. Helped me review, get up to speed and build on his code. Very clear, concise and top-notch. And English is not his primary language and yet a very impressive presentation.
Thanks, Jeffrey.
This is the simplest and the greatest example for dynamic vlookup uses on VBA. Appreciated sir.
Thank you!
That's a great video. Have seen a lot of Vlookup VBA videos. But this is the only video that gives the solution for handing the vlook error.
Thank you, Ahsan. Glad you like it :)
Looked around forever before coming across this video. Works great, thanks a lot!
Thank you, Simon, glad it was useful.
@Karen Tateosyan you Just saved me a lot of time. I adapted your code to my work project in order to apply Vlookup in another workbook and it is life-saving. Been looking for something like this for days and it is perfect. Thank you for sharing this
Thank you, Yannick, glad it was helpful :)
I can't but thank you. Thank you so much for this tutorial. It helped me a lot.
This was perfect! Well done and thank you, exactly what I needed.
Thank you, Jim.
Me too
Thanks for this. Really nice & systematic way of presentation for code application
this is what i'm looking for, thank you!
Thanks Karen, Its really helpful for me
You are the best! Less than 10 mins the VBA script? My god unimaginable skills! Thanks a ton indeed. You made my life much easier! :)
Thank you. I'm glad it helped you.
@@KarenTateosyan One question, if we have write "Invalid" for those where Vlookup values did not match. May i know the VBA script for the same? Instead of seeing only blanks.
@@supriyaprakash134 You can do this in many ways, using and IF statement, or you can simply replace in the range with results the empty values with "invalid". If you have access to the Xlookup - you can use this instead of Vlookup as it has argument "if_not_found" which you can use directly for this purpose. Hope this helps.
Thanks a lot @@KarenTateosyan
Great video. Exactly what I needed. Thank you so much.
Thank you!
This was amazing! Saved me SO much time! Thanks!!!
Thanks for the comment, glad it helps :)
I did spend two days in a row trying to figure it out. you did it in just 15 minutes. thanks mate
Thank you, Nicolas.
Thank you. It was quite helpful.
Thank you very much for this video , exactly what i needed after spending few hours on task :)
Thank you, glad it worked for you.
Karen - This was absolutely brilliant man, thanks so much from the US. I am subscribing!
Thanks, Patrick, appreciate it.
thank you from Turkey bro, this is what i'm looking for.
Thanks, Hakan.
Thank you so much. This video really helped me with what I needed !
Thanks for your comment, Dashini.
Superb mate !
very nice tutorial! was explained very well. Thank you.
Thank you, Matthew!
Hi Karen,
Really so help full your videos Thank you so much.
Thank you :)
Thank you for your video! It is very helpful. Hope you will continue making more videos about VBA😊
Thank you.
excellent explanation! thanks a lot!
thanks, Giyora
thank you , it was so helpful :)
you welcome, my friend :)
amazing video! thank you :)
Glad you liked it!
Thanks for sharing informative video... really helped lot
Thanks for commenting.
Thanks for this man!
You're welcome
Thank you!!! very helpful
Thanks
Very nice Video.. Super Helpful :)
Thanks, Disha :)
Thanks dear, your video created interest..
Glad you liked it
THANKS SI MUCH KEEP MAKING VIDEOS VERY HELPFUL AND MITIVATING
Thanks for commenting.
Karen, your lecture is helpful to sove my task. Let me get more information if there are more than 1 column. For example, I need to find target result not just only vlookup(value,A1:B100,2,false) but vlookup(value,A1:c100,3,false), vlookup(value,A1:d100,4,false) so on.
hi and sorry for the delay. Could you pleade clarify your issue as I'm not sure I understand exactly to be able to assist
This code kills, works perfectly.
Thanks, Rick :)
@@KarenTateosyan thank you
it is great, sir. thanks.
Thank you
Many thanks for this video
Glad it's helpful.
Thanks for presentation
My pleasure
Great! Thanks
Hello! Thanks you for the video. Quick question. What is the symbol you are typing after "U" and before x:
On Error Resume Next
goalsws.Range("U", & x)
Is it supposed to be an ampersand?
Thank you!
Hi Joseph,
Yes, it's an ampersand and there's no comma there. Let me know if I can assist further.
thanks very much helpful have helped me
thanks, Karan!
@@KarenTateosyan i have a question : in case i have multiple sheets and the number of rows are different then in that case how it will look for the last row.
Hi @Karan, and sorry for the delay in reply but I didn't get notification about your comment. A simple solution would be to different variables for the last row for the different worksheets.
I love your video
Thank you!
Thanks Lot
Very Nice
Thank you
Thanks for the video really helped. One question though, what would you do if you are looking up multiple columns instead of one. Would you just redo the process for each new column/parameter or is their a more efficient way to do so like grouping them together. Cheers
Hello John,
I would probably "redo the process" as with the speed of today's computers the performance wouldn't be such an issue. But for sure, maybe there's a more efficient way. I have seen people concatenating several columns into one and then using just a single v/xlookup but I don't think that this will improve things drastically.
We can do match function if possible?? Any comments?
Hi Karen, thank you for this helpful, easy to understand video! I have a question hopefully I could get an an answer for it: is it possible to let the code count only the filtered rows? I have filtered the table as usual hoping the code will jump to the visible rows only but unfortunately it is not working.
Thank you again!
Hi Sarah,
I believe there is such possibility, I don't recall exactly but there should be built-in names for the filtered range area so you can use them to look up. Still, this is something I don't recommend as I can lead to huge mess. What I would do is to copy the filtered range in a temp sheet, do what I need to there and use the data I'm interested in from the temp sheet.
@@KarenTateosyan thanks for the quick response. I managed to achieve the needed result. However, since I am new to VBA I actually need some help or guide if possible to improve the code to get exactly what I am aiming for:
1- the ability to fill in multiple columns for the same row
2- use if statement for comparison when value doesn’t exist in the “goal range” (same range name in your example) then add new row and copy data from “data range” and paste it in “goal range”
I know I am asking for too much but the original code is working fantastically and I couldn’t find a proper solution to improve it the way I need
Many Thanks
Thank you so much for the video.. I'm wondering if it's allowed to use 2 or more vLookup function in a Workbook. I'm trying to set another vlookup and I keep on encountering an error... :'(, I have 6 Worksheets. 1st Vlook up was to look for a data from the 3rd worksheet and the second vlookup to the 4th Worksheet..
Hi Cesar,
Thanks for the comment. Sure, you can use Vlookup as much as you want. What kind of error(s) you are getting? If you can provide more info and/or your code/formulas, I might be able to help :)
I can really use your help.
Hi thanks...i was looking for this. It worked wonderfully. One question, how we can use the same macro to update multiple sheets with same data in same workbook from master sheet.
Thanks
Hello ..thank you for the video. In case that I want to display a MsgBox on error, by not founding a name, what would be the code for that?
Hi, could you please clarify as I'm not sure I understand your requirement?
Thank you for great explanation , what if i have the Column to be in the middle like D instead of Column A, how do i replace this datalrow = dataws.Range("A" & Rows.Count).End(xlUp).Row ?
we have some Excel sheets with Employee Id in the middle instead of A column , i dont want to rearrange the columns to do Vlookup using VBA , any help or advice appreciated ?
Hi VIjaya,
You can use Xlookup if you use Excel 365. You can also use Index and Match combination - you can check my other tutorial in regards to this.
@@KarenTateosyan Thanks for you response ,i need to do Vlookup for the column S from First sheet with Column M in another sheet by adding Extra column after S in the First sheet and if i get N/A in any rows after doing Vlookup i need to move those N/A rows to Third sheet and remove those from first sheet and remove the added extra column from first sheet, could you please guide me in this issue ,i need to automate using macros, I am using Excel 365 .Any help will be appreciated .
@@vijayasrinivas3200 Hi,
After Vlookup-ing, I would sort the data in a way which will push the N/A rows at the bottom of the range, then find the first the N/A row and cut the range to the new sheet. Unfortunately, I cannot write the code here, so I suggest you record a macro and adjust it in accordance to your needs.
@@KarenTateosyan Thank you so much , I have tried Recording it .It works well with the column but when i add new rows to the sheet and it doesn't do vlookup and dont Show N/A for the new rows.I very much appreciate your time ,Thanks Again.
Hi! This is very helpful! May I know how can I access data if it is coming from a separate excel file and not from a worksheet within a file... thanks!
Hi,
You can open the other Excel workbook, do what you need to do and then close it, all with a code. I have a video called "Manipulating Closed Workbooks" - you can watch it, hopefully, it will help you.
Lovely
Thank you
Hi, cheers for this I struggle with this task for “n” amount of hours now! 😌 How could this be upscaled to lookup from WB_1 to WB_2?
hi Martin, could you please clarify your question :)
Sorry Karen, I’ve realised that I need a different solution to my data “vlookup”. I have two workbooks (wb1 and wb2). First one has over 40k rows times by 15cols and the other (wb2) has over 200k rows by 18 cols and I need to pull some data in based on a common key in each WB. I think I need to choose “an array looping” solution to this problem not a worksheetfunction.vlookup. But either way thank you for a great vid! 👍 Martin.
Hi Karen
your video is awesome i tried and executed it... but i have another query ? how to copy the specified cell from one workbook to another workbook through VBA macro
Can you help in this ?
Hi Shyamsundar,
Let's say you need to copy cell A1 of sheet ABC in workbook Book1 and paste it in cell C3 of sheet XYZ in workbook Book2.
You can achieve this writing the following:
Workbooks("Book1").Worksheets("ABC").Range("A1").Copy Destination:=Workbooks("Book2").Worksheets("XYZ").Range("C3")
if your files are saved, you need to include the extension in the name, i.e. istead of "Book1", it should be "Book1.xlsx" or "Book1.xlsm" for instance.
Both workbooks need to be open for this to work.
Nice and articulated perfectly.
I followed the same and unfortunately for me its taking infinite time just for 25k line items lookup. :(
hi Abdul...this is not normal...may i see your code
@@KarenTateosyan Sub Vlookup()
Dim Susp As Worksheet, Agin As Worksheet
Dim SuspLR As Long, AginLR As Long, datarng As Range, x As Long
Set Susp = ThisWorkbook.Sheets("Suspended")
Set Agin = ThisWorkbook.Sheets("Aging")
SuspLR = Susp.Range("A" & Rows.Count).End(xlUp).Row
AginLR = Agin.Range("A" & Rows.Count).End(xlUp).Row
Set datarng = Agin.Range("A2:B" & AginLR)
Application.ScreenUpdating = False
Susp.Range("H1").Value = "Account_Number"
Susp.Activate
For x = 2 To SuspLR
On Error Resume Next
Susp.Range("H" & x).Value = Application.WorksheetFunction.Vlookup( _
Susp.Range("A" & x).Value, datarng, 2, False)
Next x
The Aging sheet have 600k line items though but i only want to do a vlookup in suspended sheet having 25k lines
Hey sir. I've got a another question. I'm sorry if i bother you but just trying to learn :)
So we've just got a loop with a vlookup function which tries to exact match(false).
But if there is no match in our table for vlookup array our code gives an error. So i think we should set up an if statement for our vlookup loop. That if statement should provide us that it will give an promt if there is no match in vlookup value...
I tried to add some if codes but they did not work. I need your wise knowledge here :) If you can show me directly codes like this it would be awesome. 👇
Worksheets("MainPage").Select
i = 2
Do While i
Hi and I'm really sorry for replying a year later, but I didn't receive notification for your comment.
I'm sure you figured this out on your own already but, here's how I usually do this: I would declare a variable and use it to store the result of the vlookup function. Then I would use the isError function to check if there is an error in it, like below:
dim v as variant
...
v = application.vlookup(...)
if iserror(v) then do this else do that...
Let me know if this helps and once again, sorry for replying only now.
Please tell me.....if the datarng as blanks ....this macro is updating the blanks also......... but it should not update blanks.....only datarng has value it should vlookup..... please suggest how to ignore blanks in datarng
Sorry, but I'm not sure I understand, please clarify.
Sir i've just got a question. It works when i've done these all procedures and it finds the correct value. But when i closed the file then open the file and when i enter a new value it does not search automatically in table array. So i think we have some missings to make it more automaticaly for ''vlookup loop''. Do you have any idea how to deal with that?
Hi, yes, this is the expected behavior with what I've shown in this example. There are several ways to make Excel automatically lookup - you can either use an Event procedure, or you can update the cells in the loop to appear as formulas rather than values. Hope this helps.
I have 50,000 More data, I tried vlookup with macros that match in the video, it turns out the vlookup can't read until the end of the row limit. is there any solution help me in solving this problem
Hello,
I'm not sure I understand, could you please clarify what do you mean by "can't read until the end of the row limit"?
Hi, what if you wanted to do a VLOOKUP across specific sheets in the workbook, but it is seven sheets in total? Can you assist?
Hi, it really depends if the structure of the sheets is the same. In such case, you can use a loop within a loop to loop in the specific sheets and do the vlookup as you need. The other option is to use static references for the other sheets, but the code will be long and messy. It all depends on the specific Excel file you work with. Hope this helps.
Hello, I find your video interesting, but I still have a question, I have 3 worksheets in which I want to search, I also want to put what I want to paste in the right place, in my excel worksheet I indirectly use the correct column search, can you help me?
Hi Gerard,
I will have to take a look at the data set, but from what you describe, I believe it will be best to loop through the worksheets collection in order to get the data you need. If the different worksheets are not with the same column structure, you may have to write additional function to return the relevant column of the values you're interested in. And if those columns are not to the right of your lookup values, you may need to use other functions like Index + Match or Find + Offset.
Hope this helps.
Hey Karen, for some reason when I run your code it always tell me that the table where I want to input information only has one row. any advice.
goalslastrow = goalsWs.Range("A" & Rows.Count).End(xlUp).Row
Hi Christopher,
Unfortunately, without knowing the data you use and the entire code, I'm not sure I will be able to assist.
Hi, thank for sharing your code. I have a problem, the code is not show debugging, but when i run the code, it shows nothing. Can you please suggest ?
hi, without knowing the data your working with, I'm not able to assist
I get an error on the Last row = range line : formulasLastRow = formulasws.Range("A" & Rows.Count).End(x1up).Row
any help?
Hi Stephen,
From the line you pasted I see that you have a typo in the End propery - it's not x1up, it should be xlup. So please change 1 with L there and let me know if it works.
@@KarenTateosyan thank you it did work. My next issue is that i have 6247 rows. I have this vlookup working on 7 columns. A normal function would make the file size larger but work almost instantly. this vba is taking 10 minutes to run the vlookup on 7 columns while making the file size smaller. is there a setting i need to make for the vba to work instantly?
@@guardian8614 6 thousand rows isn't that much to be honest. Sure, it won't be instant but 10 minutes is way too slow. Unfortunately, without knowing the code / data structure I'm not in a position to assist, but if you Google it you will find many suggestions / articles what to do and what to avoid in VBA to speed up the performance.
thanks for posting this! Could i get the code somewhere? Thank you so much
Hi, the code is only a few lines, I believe if you simply retype it it's going to be way more useful than just copy-pasting it.
Sorry sir it working fine i did not update proper code
Hi Karen, I have 2 Worksheets need to validate the data by using Vlookup ( data Referral Column in both the sheet is G output need in H Column), written code but getting error (after applied Error code, its running empty ), kindly need ur help
Sub Vlookup()
Dim Goalws As Worksheet
Dim Dataws As Worksheet
Dim GoalsLastRow As Long
Dim DatasLastRow As Long
Dim X As Long
Dim DataRng
as Range
Set Goalws = ThisWorkbook.Worksheets("IBP Work sheet")
Set Dataws = ThisWorkbook.Worksheets("APO Work Sheet")
GoalsLastRow = Goalws.Range("G" & rows.Count).End(xlUp).row
DatasLastRow = Dataws.Range("G" & rows.Count).End(xlUp).row
Set DataRng = Dataws.Range("G2" & DatasLastRow)
For X = 2 To GoalsLastRow
On Error Resume Next
Goalws.Range("H" & X).value = Application.WorksheetFunction.Vlookup( _
Goalws.Range("G" & X).value, DataRng, 1, False)
Next X
End Sub
Hi Pushpa,
I think you have a mistake with this line:
Set DataRng = Dataws.Range("G2" & DatasLastRow)
Try changing it to and let me know if it works:
Set DataRng = Dataws.Range("G2:G" & DatasLastRow)
Also - don't name your subroutine "Vlookup" - choose a different name like "myVlookup" for example as it's not a good practice to use the names of built-in functions in Excel.
And lastly - I recommend you to change to the names of the varialbes you use to something more relevant for your project. In my video I have used GoalsWs as this was relevant for my dataSet, but if you don't use names that are meaningful for your project, it will be difficult to maintain in the future.
Hi Karen,
Defiantly I work on naming Conventions, Thank you so much for information :).
I changed my coding as for above guideline -
Set DataRng = Dataws.Range("G2:G" & DatasLastRow)
Now m getting error in this same line as - Method 'Range' of object'_Worksheet'failed
can we do this for two seperate files ?
Hi Anel, sure you can, but the 2 files need to be open at the same time. If one is closed you can open it, do the loop and close it, all within your code.
Hi karen
Thanks, your code is really good but I am facing an issue with result I am not getting anything using same code.
Hi Rubal, please paste your code here to see what could be wrong
Hi Ajay how do we set vlookup formula to big project. It is possible?
I think you're confusing my channel for Ajay's...anyway - how big is your project?
@@KarenTateosyan Sorry Karen.. And thanks for reply.. I need to run this formula to entire sheet that is sheet1 to sheet2. Formula =IF(ISERROR(VLOOKUP(A2,Data!$A:$T,12,0)),VLOOKUP(A2,Data!$U:$AE,3,0),VLOOKUP(A2,Data!$A:$T,12,0))
I have this... but it is not working. What did I miss? I'm getting the #N/A error.
I'm trying to pull from another worksheet (ideally), but testing with the worksheet on Sheet 2 to make sure I can get the code to run correctly.
Sub Vlookup()
Dim goalsWs As Worksheet, dataWs As Worksheet
Dim goalsLastRow As Long, dataLastRow As Long, x As Long
Dim dataRng As Range
Set goalsWs = ThisWorkbook.Worksheets("Invoice Detail")
Set dataWs = ThisWorkbook.Worksheets("Rate Sheet")
goalsLastRow = goalsWs.Range("A" & Rows.Count).End(xlUp).Row
dataLastRow = dataWs.Range("A" & Rows.Count).End(xlUp).Row
Set dataRng = dataWs.Range("A2:F" & dataLastRow)
For x = 2 To goalsLastRow
On Error Resume Next
goalsWs.Range("AL" & x).Value = Application.WorksheetFunction.Vlookup( _
goalsWs.Range("A" & x).Value, dataRng, 6, 0)
Next x
Hi Maryann,
i recommend you to change the name of your sub to something else, like My_vlookup, i.e. - it's not a good practice to use names which are reserved by Excel. Other than that - I don't see any issues with your code - do you get the N/A for all cells?
Hi sir I followed same report but look up is not happening
Hi Naga,
Could you please paste your code to check what might be the issue?
hiii karen,
this video is really nice , but i want to apply Below Formulas in VBA
=IF(ISERROR(VLOOKUP (lookup_value,Table_array,Col_index_num,[range_lookup)),Vlookup(lookup_value,Table_array,Col_index_num,[range_lookup),Vlookup(lookup_value,Table_array,Col_index_num,[range_lookup)
Please give some ideas or make some videos to understand for me.
Hi Esai,
Good idea, maybe I will create a video on this when I have some free time.
Hello, the video is interesting, I don't know that this will work for me, I have a worksheet "MENU", on "B2" I have a dropdown list with a reference to all 200 other worksheets, in "B5" I want to start searching the formulas that I now have in Excel are not in VBA
=vlookup($A$5,indirect($B$2),2,false), how can I do this in VBA, if I already know this I think I can move on. Can you help me please.
Hi, without knowing the exact workbook I cant help much.
i tried including the line "on error resume next" but it ain't working. now i'm stuck
Hi, without knowing your code I'm unable to assist much.
Please send me a sample excel sheet. Becuase i am getting some error during coding
Hi, due to copyright issues I am unable to post the excel sheet here, but if you send me your copy I may be able to advise on the errors.
Can we do this in Power query?
Not sure, I'm not too competent in Power Query.
@@KarenTateosyan thank you for the response 👍
If I am working in big data sheets. And I want
to get all data from vlookup in one cell
Like
First sheet
First column second column
Favourite fruit apple
Favourite fruit orange
And vlookup answer must be in second sheets
Favourite fruit = apple,orange(in one
cell)*
·
I can get answer from pivot
table but in different cell. But , I need all answer in one cell. Like CONCATENATE
of column. But not like every cell.
·
What I am doing is, copiyng all
data from column then copy in notepad or word,then paste it in single cell by
clicking F2.
·
I was trying concatenate with
transpose formula. But it needs all manual entry
Please help me with this. Or provide me
your mail ID so I can send you my working sheets.
Hi,
Sorry for the delay. One way to do this is to sort the data you're looking up and then to loop and use textjoin function. Another way is to create your own custom function (alternative to vlookup) which allows "matching" multiple values and not the first one. I may create another video on this when I have the time.
It shows runtime error '6':
Overflow
Hi Makani, how many rows does your data contains?
@@KarenTateosyan 50 thousands plus
@@makaniharesh1459 Could you paste your entire code - I guess you have a variable of Integer instead of Long data type....
thanks, sir code working. i need vba code for double vlookup formula vba code =if(a2=VLOOKUP(a2,table1,1,true),VLOOKUP(a2,table1,2,true),"NA")
here we used this vlookup formula for applying above 4 Lakh data
its my work in office
thankyou ( waiting for your replay )
You should have chosen simple and shorter names for your sheets and tabs
you're right but this was years ago lol
Hi, I followed your code, it is running but not sure why it's not copying from another sheet to the main sheet.
Do I need to activate both sheets or do anything?
Sub check_MACAddress()
Dim wsMain As Worksheet, wsPrevious As Worksheet
Dim mainLastRow As Long, previousLastRow As Long, x As Long
Dim dataRng As Range
Set wsMain = ThisWorkbook.Worksheets("Data")
Set wsPrevious = ThisWorkbook.Worksheets(4)
mainLastRow = wsMain.Range("A" & Rows.Count).End(xlUp).Row
previousLastRow = wsPrevious.Range("A" & Rows.Count).End(xlUp).Row
'wsMain.Columns("D:D").Insert
'wsMain.Cells(1, 4).Value = "Previous IP"
'wsMain.Cells(1, 4).Interior.Color = vbYellow
Set dataRng = wsPrevious.Range("A2:C" & previousLastRow)
For x = 2 To mainLastRow
On Error Resume Next
wsMain.Range("D" & x).Value = Application.WorksheetFunction.VLookup(wsMain.Range("C" & x).Value, dataRng, 2, False)
Next x
End Sub
Hi,
How many worksheets you have? And does one of them is with a name "4". If so, please try referring to it as Set wsPrevious = ThisWorkbook.Worksheets("4") - actually if it won't mess your data you better give it a proper name.
You better debug your code step by step with F8 and see where exactly the issue is.
@@KarenTateosyan hi, i am referring to the sheet index. Can I refer to the sheet index or it needs to be named?
@@Macarons7 You can refer to a sheet by its index as long as you're sure that it is indeed the 4th one as in your case. I'll recommend you to debug your code and see what's wrong with it.
@@KarenTateosyan right now the code is running but the vlookup function is not returning any value.
Thanks karen sir... You teach very ❤️🩹❤️🩹❤️🩹
It is giving me error in this step Set goalsws = ThisWorkbook.Worksheets("Goalscorers")
Any Advise?
Hi, without seeing your full code + data set, it's difficult to advise. You can double check to verify you don't have some typo...