Learn Excel - Video 275- VBA - Vlookup with Loops - Advance Lookup

In this video we are talking about vlookup function with loop . This is an advance level vba programming.
How to use loops
How to use vlookup in vba
how to use vlookup with loops in vba
My courses are available on my website now. You can purchase entire course or selective topics also on www.excelvbalover.com.
Any questions . please email me at [e-mail]

[@bijinzachariah9240]

Thank you so much for these lessons.. Very dynamic and love the way you explain cases and possible errors as we code along.

[@MohAboAbdo]

Thank you very much for this great video and for this simple, clear and cool technique.

[@ajayrawat8311]

Thank you, Sir. Very detailed lesson. I just wanted to know how can we use the match function with the lookup in VBA to make it more dynamic and less dependable on the excel sheet.

[@ojas2020]

You are just awesome

[@alitronics7202]

Bro no kidding this changed my life by the way if the outputed value is string you can not see the value on the screen “watches” it does only generate for integers

[@AjayKumarparmar]

Happy for you

[@Yogeshkumar-jm2mr]

Really awesome and very useful.

[@rakeshkumarmadella7955]

Excellent sir.. I really appreciate your work.. Thank you so much! I have a lot of passion to learn excel I have joined vba classes in May2017, it is very commercial . I was searching for some videos from the last 6 months in utube... suddenly last week I found one of your video... from last 1week I have watched all your Videos relentlessly... and I have really enjoyed it very much... now I am felling very confident to write Macros... now I want to do certification in VBA... Also I want to learn SAS.. please let me know if there are any utube channels like yours... once again thank you.. you are doing a Awesome Job.... Take care.

[@AjayKumarparmar]

WOW wow...so much appreciation for my work... thank you so much.. it's wonderful... I wish you had taken online training from me personally And for sure would have been disappointed.... Go for Access Vba, SQL server now.... search on KZbin if you need free learning... you need to decide who is a good trainer... there are many channels... I have not much information around it....

[@virajmalvankar97]

Hello Ajay Sir Can you make vedio on vlookup where value are horizontal and make loop for this value

[@rakeshkumarmadella7955]

This is perfectly real time scenarios.. I have also made the same code.. However got stuck at Columns numbers matching match formula in vba helped Simple grt.. I would take a bow

[@AjayKumarparmar]

Cheers

[@rajeevparmar3270]

Great video sir

[@AjayKumarparmar]

Thank you

[@imranbhatti8580]

Awesome! Ajay you are Guru. Learnt form this video why the error "Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class" occurs.

[@AjayKumarparmar]

great to hear that you learnt something new today...cheers..

[@mukundpotdar9849]

Hi Sir, This is one of my favorite video, I really enjoyed with way of your explanation. Its very very useful in our daily work purpose. Instead of doing manually.

[@k.k.sabariraj6484]

hi sir, thank you for this video presentation. here I need one more vba code for this formula =if(a2=VLOOKUP(a2,table1,1,true),VLOOKUP(a2,table1,2,true),"NA") applying this formula for the above 4 lacks data we vlookup the values from the next sheet table data waiting for your code thank you.

[@redhaakhund1271]

👍👍👍👍👍🙏

[@IrfanKhan-wv8rh]

excellent sir..

[@AjayKumarparmar]

Thanks a lot

[@ORANGEPICTURESOFFICIAL]

Sir... Please give idea.... Instead of directly writing match formula in at 5 the row green color..... How define match formula in code. I tried lot.. But not getting. Plz help to make dynamic

[@avijitnandy6662]

Hello Sir, This is awesome. Is it possible to use indirect function with vlookup and loop. If possible let me know how to do. it will be a great help.

[@imranbhatti8580]

Keep up the Excellent work,I would say.

[@AjayKumarparmar]

thank you Imran...welcome to the channel..

[@ravichauhan3933]

good good good................................very very good

[@AjayKumarparmar]

Thank you very much

[@gerardvaneggermond9967]

'Hello, I found this interesting in the worksheet that I want to do in worksheet "1" I have numbers at the top in row A (which runs to column AZ). the numbers are from 1 to 20, for example, each number has number 1 starting from columns A1 to F1 and from rows A1 to A21, now I want C3, C4, C5 data plates that I get from worksheet "MENU" on D6: D8 the number I get from Cell B4, could I use it with the formula in the video or another?

[@mout001]

Every Month I need to copy a range of data from one workbook to another workbook. In the column are the Months (Starting from April 18 till May 21). In the source file the Months are in row 2 (starting from E2, F2, G2, etc.) In the active file the Months are in row 5 (from CJ4, CK4, CL4, etc. till DU4).When the Month is ended I need to copy a range of 27 cells (row 3 till row 29) from the source file into the active file the column is based on the selected Month in cell (CI2) from the active workbook. The range is always 27 cells.So in the active workbook, I first select a Month (e.g. Aug 18) and then I click on a button to activate the copy function.At the moment I used IF, and ElseIf functions but this is a very long code. Can you help me to program a combination of a Vlookup with a loop function or something. There must be a much easier way to do this than I do now...right? Active Workbook Source file I hope you can help me

[@gerardvaneggermond2067]

Dear, I found the video interesting, but I need a little help, I have a file in the magazine "WEDSTIJDEN" have 2 columns in colom A I have 7 rows in the rows I have grades at A2 = 1 these can change to Depending on the figure that I put in B1 in the cells B2 figures are brought in, what I want to do now is to ensure that the 6 digits placed in the cells B2 to B7 in the grid are 7 columns and 31 rows the columns begin at AQ to AW. The number in B1 = 1 to 30, so every time I enter the column B numbers every time I click on the right place is that feasible?

[@AjayKumarparmar]

i am not clear about your question but what i understood is you need some auto system in spreadsheet which either fills some cells or selects the cells on some action which happens on other cell. I request you to go and see my playlist excel vba events and see if vba events can be of any help to you. if any point in time you like to join my training pls also see www.excelvbalover.com

[@udaykiran52]

I have 10 different groups in one sheet. For these groups division i need to get Code from another file through Vlookup. But every day based on group data range will be changed then how can i use vlookup in VBA.

[@harumfebrihastutidewi576]

what if the range source is from another workbook?

[@vimaltyagi6501]

Very good video kindly uploaded video in hindi

[@Ali-ds5iy]

question though: wouldn't it be easier to simply use Vlookup on the first column and then simply drag the values for the looked up column across all columns?. (I understand if you want to use the above to explain as an exercise for loops though).

[@AjayKumarparmar]

You sharpen brain by learning methods not thinking if they are right for this task or not... If you know things only then you can use anywhere else not

[@anjalibhardwaj6476]

Hi please let me know how to use VLOOKUP with MATCH function including FOR loop for all columns. Example- I want to lookup from sheet 2 to sheet 1(sheet 1 already holds data with respective headers) we just need to lookup & then compare the original values of sheet 1 with lookup values which comes from sheet 2 in TRUE /FALSE from & highlight those values so that we can compare the value which didn't match.

[@AjayKumarparmar]

Playlist name is excel vba function

[@tonyjoystrj]

Excellent, can you please build a macro with both IF & VLOOKUP functions, where there are 2 set of datas with funds to be collected and funds we actually recieved from the borrower.

[@krisztinatoth8424]

Dear Ajay, I have tride out your macro and it was very cool when I use it in one workbook from sheet to sheet. Then I used it between 2 workbooks and I got the num. 1004 error message even though I used the code for this case. Could it's possible that the "on error togo" doesn't work in the 2. workbook where I search the information? Thank you for your answer!

[@AjayKumarparmar]

Kris .. vlookup when used with different file, file n names should come in range parameter of vlookup function. Error handlers should work.. That should not be a problem. Which line throws error.. Paste it here..

[@krisztinatoth8424]

This is the code: For q = 2 To lstrow On Error GoTo nemtalál8: If Cells(q, h_nev.Column).Interior.ThemeColor xlThemeColorDark2 Then Cells(q, h_nev.Column).Value = WorksheetFunction.VLookup(Cells(q, 6).Value, Workbooks(nyisdmeg_név1).Worksheets("helyisegkonyv").Range("A1:O" & lstrow4), 2, 0) End If Next nemtalál8: If Err.Number = 1004 Then Cells(q, h_nev.Column).Value = "nincs érték" Resume Next Else End If And this line throws error: (this is 1 line in my macro, only the youtube separates it) Cells(q, h_nev.Column).Value = WorksheetFunction.VLookup(Cells(q, 6).Value, Workbooks(nyisdmeg_név1).Worksheets("helyisegkonyv").Range("A1:O" & lstrow4), 2, 0)

[@sK_YasH_7773]

Thenks Sr ......very good.....Sr what can I do to for N/A error in vlookup running in vba

[@AjayKumarparmar]

Use error handlers

[@sK_YasH_7773]

Sr how can use

[@rakeshkumarmadella7955]

Wow wow wow..

[@AjayKumarparmar]

Thank you sir

[@chanu312]

How to get the data from multiple sheets in a workbook ( eg: in a Work Book, Sheet1 has to get data from sheet2,sheet3 and sheet4),

[@AjayKumarparmar]

Visit playlist Excel Vba file handling. One more playlist.. Excel Vba workbook and sheet handling

[@amulyadav0112]

Sir, Kindly share your code in your discription i got error some time and that will help

[@theshieldfitnesspractice3144]

Dear Ajay... How will we pick the data from another workbook by vlookup vba. What will be formula for as "workbookfunction" or "worksheetfunction"

[@AjayKumarparmar]

Please check another vlookup video under playlist Excel Vba function

[@imranbhatti8580]

Hey ajay. Thanks a lot for useful videos. Want to watch your video on Excel VBA Arrays if you have uploaded one.

[@AjayKumarparmar]

you can check out the videos under excel vba arrays playlist...

[@imranbhatti8580]

Thanks ajay. If I want to learn complete Excel VBA ,in what sequence should I watch your videos.I am an intermediate level Excel VBA user and can handle the minor tasks of VBA

[@AjayKumarparmar]

First option is you can join my live online classes (excelvbalover.com). Second, if you want to use channel for free learning...this is how you should start.... Excel VBA Introduction , Excel vba loops, excel vba collection loops, Excel vba functions, excel vba workbook handling, excel vba file handling, excel vba arrays, excel vba connecting outlook ,excel vba connecting powerpoint, excel vba connecting access with excel , on the other side, keep watching excel vba access interview playlist as well. This will give you the idea what questions are asked in the interviews. Best of luck..

[@imranbhatti8580]

Your videos are also structured as a class lectures.Thanks for providing me the sequence.May I exclude the introduction as I have struggled with the very basic things for the last 2 years, or do you prescribe this as well ,for me as an intermediate level VBA user?

[@AjayKumarparmar]

You should watch. You will be able to connect better and my basic videos are not so basic. Some unexplored features are discussed as well. Thats my advice...

[@faheemkhan5177]

GREAT

[@AjayKumarparmar]

Thanks

[@ishwarshengute6888]

if emp Id not found then how can write code

[@rajeevparmar3270]

I have a question if my data have wildcard character like *, ? Then how will use Vlookup

[@AjayKumarparmar]

Explain more please

[@kumargoodvideoscreation9582]

Sir ये video का हिंदी version का link share कीजिये

[@manjeetkumar52]

Hi Sir, I am looking for Vlookup with Choose function in VBA...Can you Please give me link of that video.

[@AjayKumarparmar]

search in vlookup playlist...you will get it

[@ricecut3535]

How to speed up this code if you are dealing with 500K to 1M rows? Note: I've added the below on my code. Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.EnableEvents = False *** my code *** Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.EnableEvents = True

[@rakeshkumarmadella7955]

Sir, I have few question on matching statements using vba.. I need your help.. I want upload that file ... however I am not able to do it.

[@AjayKumarparmar]

Discuss Here.. let's see..

[@rakeshkumarmadella7955]

Ajay Kumar there are 2 sheets I want to compare 2 sheets update the changes in d new sheets... I have solutions.. However I m not able to understand d codes

[@bhuwanupreti1133]

Could you please provide me a dynamic code fr vlookup for expand nd collapse data

[@PeggywongPeggy]

may I have the code

[@AjayKumarparmar]

I never keep the codes with me...you should learn it by making dummy one for real learning

[@ravindrapatel4591]

i have study this program and make a file containing above code as below: Sub loop_vlookup() Dim for_col As Long, i As Long, r As Long, c As Long, colnum As Long, lastcoladdress As String r = 4: c = 5: colnum = 2 lastcoladdress = Sheets("sheet3").Range("a1").Value 'For for_col = 1 To 4 For for_col = 1 To Range("XFD3").End(xlToLeft).Column - 4 'For i = 1 To 6 For i = 1 To Range("d100000").End(xlUp).Row - 3 ' Cells(r, c).Value = WorksheetFunction.VLookup(Cells(r, 4).Value, Sheets("sheet3").Range("B2:G8"), colnum, 0) Cells(r, c).Value = WorksheetFunction.VLookup(Cells(r, 4).Value, Sheets("sheet3").Range("B2:" & lastcoladdress & 8), Cells(5, colnum).Value, 0) r = r + 1 Next r = 4 colnum = colnum + 1 c = c + 1 Next End Sub but i got message "Run-time error '1004': Application-defined or object-defined error" Pl guide me in the matter, if possible pl advice in email id [e-mail]

[@AjayKumarparmar]

Which line

[@ravindrapatel4591]

at following line above error comes : "Cells(r, c).Value = WorksheetFunction.VLookup(Cells(r, 4).Value, Sheets("sheet3").Range("B2:" & lastcoladdress & 8), Cells(5, colnum).Value, 0)" Pl guide me in the matter. Ravi Patel

[@ravindrapatel4591]

Ravindra Patel 1 second ago at following line above error comes : "Cells(r, c).Value = WorksheetFunction.VLookup(Cells(r, 4).Value, Sheets("sheet3").Range("B2:" & lastcoladdress & 8), Cells(5, colnum).Value, 0)" Pl guide me in the matter. Ravi Patel

[@ravindrapatel4591]

while going through F8, Mouse Pointer on lastcoladdress display lastcoladdress=""

[@AjayKumarparmar]

lastcoladdress? what value it has...check in watch window and see

[@kcseforecast7580]

How can I add two values in sheet1 and show result in sheet2 using vba

[@AjayKumarparmar]

Please explain more in detail