Microsoft Excel Tutorial: Find Last Match with VLOOKUP!

Welcome back to the MrExcel netcast where we dive into all things Excel. I'm Bill Jelen and today we have a unique problem brought to us by Sarah from England. She says that if we can solve this issue, it will help out tens of thousands of British cattle farmers. Now, this problem may seem specific to only British cattle farmers, but the solution can actually be applied to any set of data with a similar pattern.

So, what is this problem that is causing such frustration for Sarah and her fellow farmers? Well, it has to do with the ID numbers assigned to British cattle. The first part of the number is static and assigned to the farm, but the second part is where the issue lies. It starts with a number from 1 to 7 and then repeats, but with each repetition, it increases by 100,000 and then decreases by 400,000. This pattern continues indefinitely, making it nearly impossible to manually enter all the numbers.

But fear not, because I have come up with not one, but two formulas to solve this problem. The first formula involves using the MOD function to divide the row number by 7 and then adding 1 to get the desired number. This formula can be copied down to quickly generate all the necessary numbers. The second formula involves using the IF function to check if the row number is divisible by 7 and then either subtracting or adding the necessary amount to get the desired number. Both of these formulas can be used to solve this problem and save countless hours of manual data entry.

But wait, there's more! I also have a handy tip for converting these formulas into values. Simply right-click and drag the formula to the right and then back to the left, and then release the right mouse button to copy the formula as values only. This will give you the final numbers that can be used in your data analysis.

So, Sarah, I hope these formulas will help you and your fellow cattle farmers in the UK. And for all of you watching, don't forget to share this solution with anyone who may be struggling with a similar problem. Thank you for tuning in to the MrExcel netcast, and we'll see you next time for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: [ Ссылка ]

You can help my channel by clicking Like or commenting below: [ Ссылка ]



Yes - a podcast of use to 10,000 British Cattle Farmers but of no use to anyone else. Actually...it is a fun challenge. Play this one to see the problem then see if you can come up with a formula to extend the fine numbering system used for British Cattle! Episode 1029 will show you how.


Table of Contents
(00:00) Incrementing ID Numbers for British Cattle Farmers
(00:35) Pattern of numbers
(01:06) Helper column for static portion
(01:22) Use MOD function of row divided by 7
(01:38) Using the TEXT function
(02:27) Testing the formula
(03:20) Clicking Like really helps the algorithm


#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorialalightmotion

This video answers these common search terms:
British cattle farmers
Concatenation formula
Formula
ID numbers
Magic
Mod function
Pivot table
Static part
TEXT function
Values only

Join the MrExcel Message Board discussion about this video at [ Ссылка ]

свернуть