How To Automate VLOOKUP With Excel VBA

Have you ever wanted to do this in Excel?

You’d like to ‘look up’ an entry from a table. In the video, we use the example of the Doggy Football League and looking up home grounds from team names. Well, that’s easy with VLOOKUP, right? But what about this common additional requirement: usually you’d like to look up the entry, but occasionally you’d like to ‘overtype’ or enter something different. A kind of dual function. Is this even possible? This poses a real problem for Excel users because, once a formula is ‘overtyped’, it doesn’t come back unless re-entered manually. So, what to do?

🔥FREE: YOUR EXCEL CHEATSHEET MINI-COURSE

Excel VBA offers us an elegant solution. It’s hardly beginner level, however, because some complex syntax is needed to get everything working. As I show in the video, we can make things easier for ourselves and, of course, the Excel download file is available with a fully working example. Follow along with me and see if you can get it working too – it could unlock a new world of functionality in your data analysis work.

💼EXCEL DOWNLOAD FILE

It’s a little-known fact that Excel formulae such as VLOOKUP can be accessed from the VBA editor. We’re not talking about native functions in Excel VBA such as RIGHT and VAL. Rather, Application.WorksheetFunction tells Excel to find the worksheet function and harness it in Excel VBA. It gives us the power of a worksheet formula in VBA. In this video, we explore an application of VLOOKUP – but I also frequently use COUNTIF and MATCH with Application.WorksheetFunction.

📺EXCEL VBA BEGINNER COURSE (20 MINS)
📺EXCEL VBA BEGINNER COURSE (1 HOUR)
📺EXCEL VBA BEGINNER COURSE (15 HOURS)

Should we use it all the time? No, most formulae are best as they are – formulae in the worksheet. From time to time, however, Application.WorksheetFunction solves an Excel headache, as it does in this case. It’s also a great coding challenge that will stretch and enhance your understanding of Excel VBA.

The real benefit in this case is that the ‘looked up’ value (the name of the home ground) is entered to the spreadsheet as a value, not a formula. Suddenly, all our problems are solved! At the click of a button, we can pull in the values AND afford the user the option to ‘overtype’ the entry if they need to …

🔥FREE: YOUR EXCEL CHEATSHEET MINI-COURSE

Putting together Application.WorksheetFunction requires patience and awareness of a few things that make the process (slightly) easier. I recommend building the formula in the worksheet FIRST, then, as I show in the video, use the inverted comma to store the formula as text in a visible cell. You can reference this from the VBA editor. It’s important because the prompts VBA provides are not as helpful what you might be used to – as we discover in the video.

So, did you get Application.WorksheetFunction working? What applications can you see for Application.WorksheetFunction in your work? And would you like to see more videos about this topic, perhaps with other formulae? Let me know in the YouTube comments.

[TigerSpreadsheetSolutions]

🔥WATCH NEXT
🔥EXCEL VBA ARRAYS FOR BEGINNERS

[patrickdurdenman]

I will have to thank you massively, Sir. Not only was that a great tutorial from a coding standpoint, but I also enjoyed your communication and video-style, both adding to one great educational experience. Keep up the good work, I think VBA will still remain relevant for years to come since I see tons of it in my work in the banking industry on a regular basis, and businesses will want to take advantage of the Database-enhancements/queries in Excel, which can be further advanced through VBA and so forth.

[hnxtfgl]

A new and interested subscriber from Algeria 🤗 Thank you and keep it up 👌💪

[tllwrcm]

Thanks for your efforts in simplifying the information

[leewilliams]

superb video and so easy to understand

[bryanmclucas]

I would like to add another method to check if "Y" is in column B and if so then to do the rest of the code. How would I add that in?

[OmarAl]

Thanks a lot for the brilliant setup. I was wondering if you could show us how to apply the vlookup code on filtered rows. For instance, importing monthly values from report to the Master File (After implementing couple of filters to the Master Data).

Best,

[izaak]

For the variable chris cell, is there a way to select a range depending on the size and not a fixed range ("E6:E15")? Like for example, using vlookup in a weekly report that is different every time

[bookmaster]

Do you have a video in which you explain how to create multiple buttons which will record the values on a column when pressed any of it?

[oppenheimerroberta]

If I have the List sheet in a different excel, can you please help me what to use instead of the Sheets("List") part?

[alvinyudapratama]

Great Video, but can I do lookup to another workbook? not in the same file

[hamzalamnaouar]

Hello sir,

Where I reach vba code to reference the editor doesn't give me the sheets referencing as you demonstrated

[jayaramakrishna]

hi chris

this vlookup vba works for the sheet to sheet or it will work for one work book to another workbook
my priority of vlookup is from one workbook to another workbook
i have tried the code and run the F8 i am getting
run time error 1004
can we help me out

[MichUniverse_]

Hi How do I continue with next cell. I want to lookup more than 1 cell. Thank you

[iClone]

Can someone explain what he's explaining at 3:01, regarding the last two parameters of the Vlookup function? He's speaking so fast I can't interpret what he's explaining despite listening 5 times in a row.

[robbyrevo]

The video is great but there is something about the sound.

[jennyatilano]

The tutorial was easy to understand but I get error 1004 😅

[daringfables]

Bro u speak like Tyson Fury) good video, just please fix the voice and keep up)