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.

@KarenTateosyan

Жыл бұрын

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.

@KarenTateosyan

3 жыл бұрын

Thanks, Jeffrey.

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! :)

@KarenTateosyan

10 ай бұрын

Thank you. I'm glad it helped you.

@supriyaprakash134

10 ай бұрын

@@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.

@KarenTateosyan

10 ай бұрын

@@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.

@supriyaprakash134

10 ай бұрын

Thanks a lot @@KarenTateosyan

This is the simplest and the greatest example for dynamic vlookup uses on VBA. Appreciated sir.

@KarenTateosyan

Жыл бұрын

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.

@KarenTateosyan

4 жыл бұрын

Thank you, Ahsan. Glad you like it :)

Looked around forever before coming across this video. Works great, thanks a lot!

@KarenTateosyan

2 жыл бұрын

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

@KarenTateosyan

3 жыл бұрын

Thank you, Yannick, glad it was helpful :)

Thanks Karen, Its really helpful for me

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.

@KarenTateosyan

3 жыл бұрын

Thank you, Jim.

@akilant2219

3 жыл бұрын

Me too

Thanks for this. Really nice & systematic way of presentation for code application

this is what i'm looking for, thank you!

This was amazing! Saved me SO much time! Thanks!!!

@KarenTateosyan

3 жыл бұрын

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

@KarenTateosyan

2 жыл бұрын

Thank you, Nicolas.

Thank you very much for this video , exactly what i needed after spending few hours on task :)

@KarenTateosyan

2 жыл бұрын

Thank you, glad it worked for you.

Great video. Exactly what I needed. Thank you so much.

@KarenTateosyan

2 жыл бұрын

Thank you!

Thank you. It was quite helpful.

Thank you so much. This video really helped me with what I needed !

@KarenTateosyan

3 жыл бұрын

Thanks for your comment, Dashini.

Superb mate !

thank you from Turkey bro, this is what i'm looking for.

@KarenTateosyan

3 жыл бұрын

Thanks, Hakan.

Hi Karen, Really so help full your videos Thank you so much.

@KarenTateosyan

3 жыл бұрын

Thank you :)

thank you , it was so helpful :)

@KarenTateosyan

4 жыл бұрын

you welcome, my friend :)

Thank you for your video! It is very helpful. Hope you will continue making more videos about VBA😊

@KarenTateosyan

Жыл бұрын

Thank you.

very nice tutorial! was explained very well. Thank you.

@KarenTateosyan

2 жыл бұрын

Thank you, Matthew!

Karen - This was absolutely brilliant man, thanks so much from the US. I am subscribing!

@KarenTateosyan

Жыл бұрын

Thanks, Patrick, appreciate it.

excellent explanation! thanks a lot!

@KarenTateosyan

3 жыл бұрын

thanks, Giyora

THANKS SI MUCH KEEP MAKING VIDEOS VERY HELPFUL AND MITIVATING

@KarenTateosyan

2 жыл бұрын

Thanks for commenting.

Thanks for presentation

@KarenTateosyan

11 ай бұрын

My pleasure

Thanks dear, your video created interest..

@KarenTateosyan

4 жыл бұрын

Glad you liked it

Thanks for this man!

@KarenTateosyan

3 жыл бұрын

You're welcome

Thanks for sharing informative video... really helped lot

@KarenTateosyan

2 жыл бұрын

Thanks for commenting.

amazing video! thank you :)

@KarenTateosyan

Жыл бұрын

Glad you liked it!

Thank you!!! very helpful

@KarenTateosyan

3 жыл бұрын

Thanks

Very nice Video.. Super Helpful :)

@KarenTateosyan

4 жыл бұрын

Thanks, Disha :)

Great! Thanks

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.

@KarenTateosyan

4 жыл бұрын

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

Thanks Lot Very Nice

@KarenTateosyan

2 жыл бұрын

Thank you

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!

@KarenTateosyan

3 жыл бұрын

Hi Joseph, Yes, it's an ampersand and there's no comma there. Let me know if I can assist further.

This code kills, works perfectly.

@KarenTateosyan

3 жыл бұрын

Thanks, Rick :)

@rickswineberg

3 жыл бұрын

@@KarenTateosyan thank you

it is great, sir. thanks.

@KarenTateosyan

Жыл бұрын

Thank you

Many thanks for this video

@KarenTateosyan

2 жыл бұрын

Glad it's helpful.

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

@KarenTateosyan

2 жыл бұрын

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.

@ashutoshdwivedi7360

2 жыл бұрын

We can do match function if possible?? Any comments?

thanks very much helpful have helped me

@KarenTateosyan

3 жыл бұрын

thanks, Karan!

@ReactionpeAction

3 жыл бұрын

@@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.

@KarenTateosyan

3 жыл бұрын

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

@KarenTateosyan

2 жыл бұрын

Thank you!

Sorry sir it working fine i did not update proper code

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..

@KarenTateosyan

5 жыл бұрын

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 :)

@gerardvaneggermond9967

4 жыл бұрын

I can really use your help.

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!

@KarenTateosyan

2 жыл бұрын

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.

@sarahfahad9523

2 жыл бұрын

@@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

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.

@KarenTateosyan

3 жыл бұрын

Thanks

Lovely

@KarenTateosyan

3 жыл бұрын

Thank you

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?

@KarenTateosyan

Жыл бұрын

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.

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?

@KarenTateosyan

9 ай бұрын

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.

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!

@KarenTateosyan

2 жыл бұрын

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.

thanks for posting this! Could i get the code somewhere? Thank you so much

@KarenTateosyan

Жыл бұрын

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.

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?

@KarenTateosyan

4 жыл бұрын

Hi, could you please clarify as I'm not sure I understand your requirement?

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

@KarenTateosyan

Жыл бұрын

Sorry, but I'm not sure I understand, please clarify.

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

@KarenTateosyan

2 жыл бұрын

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 ?

@KarenTateosyan

Жыл бұрын

hi, without knowing the data your working with, I'm not able to assist

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?

@KarenTateosyan

3 жыл бұрын

hi Martin, could you please clarify your question :)

@martinsefelin4479

3 жыл бұрын

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.

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

@KarenTateosyan

3 ай бұрын

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.

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 ?

@KarenTateosyan

2 жыл бұрын

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.

@vijayasrinivas3200

2 жыл бұрын

@@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 .

@KarenTateosyan

2 жыл бұрын

@@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.

@vijayasrinivas3200

2 жыл бұрын

@@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.

Nice and articulated perfectly. I followed the same and unfortunately for me its taking infinite time just for 25k line items lookup. :(

@KarenTateosyan

4 жыл бұрын

hi Abdul...this is not normal...may i see your code

@abdulhaji1017

4 жыл бұрын

@@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

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 ?

@KarenTateosyan

4 жыл бұрын

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.

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?

@KarenTateosyan

4 жыл бұрын

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.

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

@KarenTateosyan

4 жыл бұрын

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"?

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

@KarenTateosyan

2 жыл бұрын

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?

I get an error on the Last row = range line : formulasLastRow = formulasws.Range("A" & Rows.Count).End(x1up).Row

@guardian8614

4 жыл бұрын

any help?

@KarenTateosyan

4 жыл бұрын

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.

@guardian8614

4 жыл бұрын

@@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?

@KarenTateosyan

4 жыл бұрын

@@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.

i tried including the line "on error resume next" but it ain't working. now i'm stuck

@KarenTateosyan

Жыл бұрын

Hi, without knowing your code I'm unable to assist much.

can we do this for two seperate files ?

@KarenTateosyan

Жыл бұрын

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.

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.

@KarenTateosyan

11 ай бұрын

Hi, without knowing the exact workbook I cant help much.

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

@KarenTateosyan

4 жыл бұрын

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.

@pushpaa5803

4 жыл бұрын

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

Hi Ajay how do we set vlookup formula to big project. It is possible?

@KarenTateosyan

4 жыл бұрын

I think you're confusing my channel for Ajay's...anyway - how big is your project?

@kiranpatil-qt4lm

4 жыл бұрын

@@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))

Hi sir I followed same report but look up is not happening

@KarenTateosyan

5 жыл бұрын

Hi Naga, Could you please paste your code to check what might be the issue?

Hi karen Thanks, your code is really good but I am facing an issue with result I am not getting anything using same code.

@KarenTateosyan

3 жыл бұрын

Hi Rubal, please paste your code here to see what could be wrong

Please send me a sample excel sheet. Becuase i am getting some error during coding

@KarenTateosyan

3 жыл бұрын

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.

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 )

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.

@KarenTateosyan

4 жыл бұрын

Hi Esai, Good idea, maybe I will create a video on this when I have some free time.

Can we do this in Power query?

@KarenTateosyan

Жыл бұрын

Not sure, I'm not too competent in Power Query.

@madhun8092

Жыл бұрын

@@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.

@KarenTateosyan

4 жыл бұрын

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

@KarenTateosyan

3 жыл бұрын

Hi Makani, how many rows does your data contains?

@makaniharesh1459

3 жыл бұрын

@@KarenTateosyan 50 thousands plus

@KarenTateosyan

3 жыл бұрын

@@makaniharesh1459 Could you paste your entire code - I guess you have a variable of Integer instead of Long data type....

You should have chosen simple and shorter names for your sheets and tabs

@KarenTateosyan

Жыл бұрын

you're right but this was years ago lol

Thanks karen sir... You teach very ❤️‍🩹❤️‍🩹❤️‍🩹

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

@KarenTateosyan

10 ай бұрын

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.

@Macarons7

10 ай бұрын

@@KarenTateosyan hi, i am referring to the sheet index. Can I refer to the sheet index or it needs to be named?

@KarenTateosyan

10 ай бұрын

@@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.

@Macarons7

10 ай бұрын

@@KarenTateosyan right now the code is running but the vlookup function is not returning any value.

It is giving me error in this step Set goalsws = ThisWorkbook.Worksheets("Goalscorers") Any Advise?

@KarenTateosyan

2 жыл бұрын

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...