You are reading the article Recover Damaged Excel Files With Recovery Toolbox For Excel updated in February 2024 on the website Minhminhbmm.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Recover Damaged Excel Files With Recovery Toolbox For Excel
This is a sponsored article and was made possible by Recovery Toolbox. The actual contents and opinions are the sole views of the author who maintains editorial independence, even when a post is sponsored.
The good news is when Excel detects a corrupted workbook, it automatically switches to file recovery mode in an attempt to repair the file. And even if it doesn’t, you can still initiate the recovery process manually. However, if the degree of damages was extreme, the built-in Excel repair tool might not be able to repair the file.
In such cases, the only way to repair and recover data from such damaged Excel files would be to use a third-party repair tool. We had the opportunity to test Recovery Toolbox for Excel to see whether it can recover data from damaged Excel files.What Is Recovery Toolbox for Excel?
Recovery Toolbox for Excel is a software utility that helps repair and restore damaged and corrupt Excel files. This tool helps minimize your hassles when dealing with corrupt Excel files.Program Features
Here’s a list of the salient features that make Recovery Toolbox for Excel stand out from the competition.
Recovers all types of Excel files: .xls, .xlt, .xlsx, .xlsm, .xltx, .xltm, and .xlam extension
Repairs and recovers .xls files of Microsoft Office 98, 2000, 2003, and XP
Recovers .xlsx files of Microsoft Office 2007, 2010, 2013, 2024
Repairs all types of formulas, including functions and name references
Recovers worksheets, fonts, table styles, and workbook cells data
Restores cells and border colors
Repairs and restores cell formatting values (number format, font, text orientation, fill pattern and more)
Directly exports recovered data to a new Excel document
Recovery Toolbox for Excel works with all versions of Windows – Windows 98//XP/Vista/7/8/10 or Windows Server 2003/2008/2012/2024 and above.How It Works
To determine how effective Recovery Toolbox for Excel is at recovering data, we’re going to test it on a severely damaged .xls Excel file. To get started, we’re going to first download and install the software, then try to recover data from the damaged Excel document.Download and Installation Recovering Data from a Corrupted Excel Workbook
If Excel can’t open a damaged file, it will display a pop-up error message that either the file is corrupted or not from a trusted source. This is the error message I got from the damaged file I was trying to open with Excel.
The following steps how how to use Recovery Toolbox for Excel to try to repair and recover the lost data.
The program will automatically start the recovery process. It will then display a preview of all the recovered items.
That’s a preview of the contents of the corrupt file that Excel couldn’t read. You can see the formatting was applied and how the file will look once the recovery process is complete.
The software will display the recovery results, and you’ll even be able to see the number of items repaired, cells processed, and the time taken to complete the recovery.
After file repair is complete, you will be able to open the recovered file with Excel. Here’s how the new (recovered) file looks when opened with Excel.
As you can see, the software was able to recover the formulas in that Excel file.Pricing
The company offers three pricing plans:
Personal License – This license is for personal use only and costs $27 for a lifetime license.
Business License – This plan is for corporations or for legal use in an enterprise. A business license costs $45.
Site License – You can buy a Site License for $60. This license allows you to use the software on several computers – up to 100 electronic devices. The devices can be in one building or distributed between several buildings.
There is also a free (trial) version, but it only gives you a preview of the recovered data. That means you’ll not be able to export the data to a new Excel workbook or save it to an XLSX file. To unlock all the features, you’ll need a license.Pros and Cons
Here are the pros and cons we found with the software.Pros
Easy to use with a clean user interface
Comprehensive – repairs and restores formulas, table styles, fonts, text orientation, number formats, and more
Does not recover inserted pictures
Cannot recover data from password protected filesFinal Thoughts
Combining a sophisticated recovery algorithm with a simple and easy to use interface, Recovery Toolbox for Excel offers great value when you need to recover damaged Excel files. Having used this software, I’d recommend it to anyone looking for an excellent Excel repair and recovery tool.
Alternatively, you can use their Online Excel Repair Service if you want to repair a damaged Excel file without having to download and install additional software.
Kenn is a tech enthusiast by passion, Windows blogger by choice, and a massive coffee imbiber. He likes watching sci-fi movies in his free time and tearing gadgets apart so he can fix them.
Subscribe to our newsletter!
Our latest tutorials delivered straight to your inbox
Sign up for all newsletters.
You're reading Recover Damaged Excel Files With Recovery Toolbox For Excel
Excel is a powerful tool that helps you store data, perform calculations, and organize information. To make the best use of the tool, you need a quick reference to guide you through the various functions, commands, and shortcuts.
This article is a comprehensive Excel cheat sheet specifically designed for beginners. It starts with the basics and moves on to functions, formulas, and data analysis tools.
Let’s get started!
We start with a cheat sheet for navigating the Excel interface, which can seem a little intimidating at first glance, but don’t worry!
This section will give you the lowdown on the essential features, shortcuts, and tricks, including the ribbon, workbooks and worksheets, and rows, columns, and cells.
The ribbon is at the top of your screen. Each tab includes a set of commands to help you perform tasks quickly
To show or hide the ribbon commands, press Ctrl-F1.
If you can’t remember the location of a command, you can always use the search bar on the ribbon to find it.
A workbook is an Excel file that contains one or more worksheets. Worksheets are where you organize and process your data.
You can navigate through your worksheets using keyboard shortcuts:
Press Ctrl + Page Up to move to the next sheet.
Press Ctrl + Page Down to move to the previous sheet.
Worksheets are made up of rows, columns, and cells.
Rows are labeled with numbers, and columns are labeled with letters. Cells are the intersection of rows and columns and are referred to by their column and row labels, like A1 or B2.
To navigate within a worksheet, use the arrow keys to move up, down, left, or right.
You can also use the following shortcuts:
Ctrl + up arrow
Ctrl + down arrow
Ctrl + left arrow
Ctrl + right arrow
Now that you know how to navigate the Excel interface, let’s go over some of the basics of the program in the next section of our cheat sheet.
You can also use the arrow keys to navigate between cells.
Press ‘Enter’ to apply your changes.
To undo, press ‘Ctrl’ + ‘Z’, or use the ‘Undo’ button in the Quick Access Toolbar.
To redo, press ‘Ctrl’ + ‘Y’, or use the ‘Redo’ button in the Quick Access Toolbar.
To move a single cell or a range of cells, follow these steps:
Hover your cursor over the edge of the cells until it becomes a four-sided arrow.
Alternatively, you can use the ‘Shift’ + arrow keys to select cells.
To find a specific value or text in your worksheet:
Press ‘Ctrl’ + ‘F’ to open the ‘Find’ dialog box.
Enter your search query.
To replace a value or text:
Press ‘Ctrl’ + ‘H’ to open the ‘Replace’ dialog box.
Enter the value you want to find in the ‘Find what’ field.
Enter the value you want to replace within the ‘Replace with’ field.
With the basics done, let’s take a look at what you need to remember when formatting cells in the next section.
When entering dates or times, format the cells accordingly before inputting the data.
To format a cell:
Choose ‘Format Cells’.
Select the desired format.
You can choose the appropriate number format to simplify data presentation. Here’s how you can apply different number formats:
Select the cells you want to format.
Common number formats include:
General: Default format without specific styles.
Number: Displays numbers with decimals and commas.
Currency: Adds currency symbols to the numbers.
Percentage: Represents the value as a percentage.
Date: Formats the cell value as a date.
Enhance the readability of your text by applying various formatting options.
When you select the cells containing the text you want to format, you can use keyboard shortcuts for quick formatting:
Ctrl+B for bold.
Ctrl+I for italic.
Ctrl+U for underline.
Alternatively, use the toolbar options in the ‘Home’ tab, like ‘Font’ and ‘Alignment’.
Apply borders and shading to emphasize or differentiate cell data. Follow these steps:
Select the cells you want to format.
Conditional formatting allows you to apply formats based on specific conditions. Here’s how to implement it:
Select the cells you want to apply conditional formatting to.
Select from the list of available conditions or create a new rule.
Assign the appropriate format for the selected condition.
In the next section of our reference guide, we provide a cheat sheet for popular Excel formulas. Let’s go!
Before we give you the most commonly used Excel formulas and functions, here is a quick primer on how to use them.
Formulas in Excel are used to perform calculations and manipulate data with built-in functions.
To create a formula, start with an equal sign (=) followed by a combination of numbers, cell references, and mathematical operators.
Here is an example that adds the values in the range A1 to A5
With that in mind, let’s look at some basic Excel formulas and functions.
Sometimes you need to chop up strings in a cell. For example, extracting the parts of an address is a common task. These text functions are very useful:
LEFT: this function returns the first or multiple characters from a string.
RIGHT: gets the last or multiple characters from a string.
MID: gets characters in the middle of the string.
CONCAT: puts strings together.
This example shows the use of the LEFT function to extract the first four characters:
The find functions provide powerful features for working with text:
FIND: locates a specific string within another text string
SEARCH: similar to FIND but works with wildcard characters
In the next section, we take a look at some popular Excel math functions.
There is a long list of Excel functions that includes some highly complex computations. Here are the ones you will most commonly use:
SUM: Adds up a range of numbers.
AVERAGE: Calculates the average (arithmetic mean) of a range of numbers.
MIN: Returns the smallest value in a dataset.
MAX: Returns the largest value in a dataset.
COUNT: Counts the number of cells containing numbers within a range.
PRODUCT: Multiplies a range of numbers together.
Here is an example of the SUM function:
Time functions help you manage, convert, and manipulate time data.
NOW: Returns the current date and time.
TODAY: Returns the current date without the time.
HOUR: Extracts the hour from a given time.
MINUTE: Extracts the minutes from a given time.
SECOND: Extracts the seconds from a given time.
TIME: Constructs a time value from separate hour, minute, and second values.
A lookup function searches for specific values within a range or table and returns corresponding data from another column or row. These are the most common for Excel users:
VLOOKUP: Searches for a value in the first column of a table and returns a value in the same row from a specified column.
HLOOKUP: Searches for a value in the first row of a table and returns a value from a specified row in the same column.
INDEX: Returns a value from a specified row and column within a given range or table.
MATCH: Searches for a specified item in a range or array and returns the relative position of the item within that range.
If you’ve got the latest version of MS Excel, there are some new features like XLOOKUP, which is faster than the older functions.
Here’s an example of using the VLOOKUP function:
Cell referencing is a way to point to a specific cell or range of cells in a formula. There are two types of cell references: absolute and relative.
Absolute refers to a specific cell or range and keeps the same reference even when the formula is copied. It uses a dollar sign ($) to denote absolute referencing, like $A$1.
A relative reference changes when the formula is copied to another cell or range, adjusting the reference based on the new location.
Formulas can sometimes result in errors due to incorrect syntax, invalid references, or other issues with calculation. Some common error messages are:
#DIV/0!: Division by zero
#NAME?: Occurs when Excel doesn’t recognize text in the formula
#REF!: Invalid cell reference
#VALUE!: Occurs when the wrong data type is used in a formula
To fix errors, check the formula’s syntax, cell references, and data types to make sure they are correct and compatible.
With formulas done, the next section of our reference is for Excel data analysis tools.
Excel provides a wide range of tools to help you analyze and organize your data effectively. Let’s discuss the various tools and their importance.
Sorting and filtering in Microsoft Excel enables you to arrange your data in a more organized way. To access the feature:
Go to the Home tab.
Choose one of the options in the drop-down list.
These are your options:
Sort A to Z: Orders text data alphabetically or numerical data from the lowest value to the highest.
Sort Z to A: Orders text data in reverse alphabetical order or numerical data from the highest value to the lowest.
Custom Sort: Apply multiple sorting conditions to your data.
Filter: Display only rows that meet specific criteria.
Pivot tables are used to summarize and consolidate your data effectively. They enable you to perform quick data analysis by dragging and dropping different fields into rows or columns and applying calculations such as sum, average, and standard deviation.
To create a pivot table:
Select your data range.
Choose ‘PivotTable’ from the drop-down menu and configure your pivot table settings.
Drag and drop fields into rows, columns, and values areas to analyze your data.
Visual representations of your data using charts
and graphs can help you gain better insights and make informed decisions. Excel comes with a handy variety of charts and graphs to choose from, including:
Column charts: Compare different data sets across distinct categories.
Bar charts: Display comparisons among discrete categories horizontally.
Line charts: Show trends and patterns over time.
Pie charts: Illustrate proportional data and percentages.
To create a chart in Excel:
Select your data range.
Customize your chart’s design, layout, and formatting to meet your requirements.
Charts and graphs are powerful tools. If you want to see some in action, check out this video:
We’ve covered a lot of ground so far! Up next is a reference for popular Excel keyboard shortcuts.
There are four categories of keyboard shortcuts in Excel:
Data Analysis Shortcuts
Here are some commonly used shortcuts for routine tasks and Excel commands:
Ctrl + N: Create a new workbook
Ctrl + O: Open an existing workbook
Ctrl + S: Save the current workbook
Ctrl + Z: Undo the last action
Ctrl + Y: Redo the last action
Ctrl + C: Copy the selected cells
Ctrl + X: Cut the selected cells
Ctrl + V: Paste the copied or cut cells
To navigate within a workbook, try the following shortcuts:
Ctrl + arrow keys: Move to the edge of the current data region
Ctrl + G: Open the Go To dialog box
Ctrl + Page Up: Move to the previous sheet in the workbook
Ctrl + Page Down: Move to the next sheet in the workbook
Home: Move to the beginning of a row
Ctrl + Home: Move to the first cell in the worksheet (A1)
Use these shortcuts for formatting in Excel:
Ctrl + 1: Open the Format Cells dialog box
Ctrl + B: Apply or remove bold formatting
Ctrl + I: Apply or remove italic formatting
Ctrl + U: Apply or remove underline formatting
Ctrl + 5: Apply or remove strikethrough formatting
Alt + H + H: Access the Fill Color options
Alt + H + B: Access the Border options
When working with data, these shortcuts can be helpful:
Alt + A + S + S: Sort selected data alphabetically
Alt + A + T: Add or remove a filter to the selected range
Ctrl + Shift + L: Enable or disable AutoFilter
Alt + =: Insert an AutoSum formula
F2: Edit an active cell
Ctrl + Shift + Enter: Enter a formula as an array formula
By mastering these keyboard shortcuts, you can navigate, format, and analyze your data more efficiently and effectively.
And there we have it. We’ve covered a lot of ground in this cheat sheet, diving into the essentials of navigating the Excel interface.
This guide is here to simplify your journey with Excel, offering you the key tools, shortcuts, and techniques at your fingertips, so make sure to save it for future reference!
Excel Accounting Number Format (Table of Contents)
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & othersAccounting Number Format in Excel Difference between Currency and Accounting Number Format in Excel
The difference between Currency and Accounting format is shown in the below screenshot.How to Use Accounting Number Format in Excel?
In Microsoft Excel, we can find the accounting format under the number formatting group shown in the below screenshot.
Also, we can format the number in accounting format by choosing the dollar sign $ in the number group, which is also one of the shortcuts for the accounting number format shown in the below screenshot.Example #1
Converting Number to Excel Accounting Format
You can download this Accounting Number Format Excel Template here – Accounting Number Format Excel Template
In this example, we will learn how to convert the normal number to accounting format. Consider the below example, which shows MRP, Selling Price of the individual product with local, national and zonal prices.
As we can notice that all the numbers are in general format by default, Assume that we need to convert the “Selling Price” to Accounting Number format along with Local, Zonal, and National selling prices.
In order to convert the number to Accounting format, follow the below procedure step by step.
First, select the column from E to H, where it contains the product’s selling price, which is shown in the below screenshot.
Once we choose the Accounting number format, we will get the output as ###, which is shown below.
We can notice that once we convert the number to accounting number format, excel will align the dollar sign at the left edge of the cell and display with two decimal points that we are getting the ### hash symbols.
Enlarge all the columns so that we can see the exact accounting format output, which is shown below.
In the below result, we can see that all the numbers are converted where we can see the Dollar sign$ in each left edge of the cell separated by commas and with two decimal numbers.Example #2
To apply accounting number formatting, follow the below step by step procedure as follows.
First, select the column from E to H, where it contains the product’s selling price, which is shown in the below screenshot.
In the above screenshot, we can see the list of number formatting options.
Select the Accounting option so that it will display the accounting format, which is shown below.
As we can see, on the right-hand side, we can see decimal places where we can increase and decrease the decimal points, and next to that, we can see the symbol drop-down box, which allows us to select which symbol needs to be displayed. (By default, accounting format will select the Dollar Sign $)
Once we increase the decimal places, the sample column will display the number with selected decimal numbers which are shown below.
This example shows how to sum the accounting number format by following the below steps.
Consider the example which shows sales data for the month of OCT-18.
As we can see that there are normal sales figures in the General number format. Now we will convert the above sales figure to accounting format for accounting purposes.
First, copy the same B column sales figure next to the C column, which is shown below.
Now select the C column and go to the number formatting group and choose Accounting, shown below.
As we can see, the difference that C column has been converted to accounting format with a Dollar sign with two decimal places and at the last column for negative numbers accounting format has shown the number inside the parenthesis.
Put the SUM formula in the C13 column, which will show the SUM in accounting format.
In the below result, we can see that the accounting format which automatically uses the dollar sign, decimal places, and comma to separate a thousand figures where we cannot see those in General number format.Things to Remember
The accounting number format is normally used for financial and accounting purposes.
The accounting number format is the best way to configure the values.
For negative values accounting format will automatically insert the parenthesis.Recommended Articles
This has been a guide to Accounting Number Format in Excel. Here we discussed how to use Accounting Number Format along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
WORKDAY Function in Excel (Table of Contents)WORKDAY in Excel
Workday function in excel returns the Date, which is the official working day from the date which we feed into the syntax. This is quite useful for getting what would the working day date after selective day counts. As per syntax, we just need to select the date from which we need to count the number of the working day, then select how many days we need to count, and if there is any week off, we have optional. If we select today’s date with 5 days and 2 weeks off days, we will get the date of the same weekday.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
WORKDAY Formula in Excel:
Below is the WORKDAY Formula in Excel.Explanation of WORKDAY Formula in Excel
A WORKDAY Function in Excel includes two mandatory parameters and one optional parameter.
Start_date: “Starting date of the project or any work”.
Days: The total number of days required to complete the work or project. This does not include weekends (Saturday and Sunday).
[Holidays]: This is an optional parameter. This section asks whether the days you have mentioned include any holidays. For this, you need to make a list of holidays separately.
WORKDAY Function in Excel by default excludes Saturday and Sunday as weekend days. If at all you need weekends for any other day, you can use chúng tôi function. For example: In the Middle East region, weekend days are Friday & Saturday. In these cases, we can use chúng tôi function instead of a normal WORKDAY Function in Excel.How to Use WORKDAY Function in Excel?
You can download this WORKDAY Function Excel Template here – WORKDAY Function Excel TemplateExample #1
Using the WORKDAY Function in excel, we can generate a series of dates even though we can generate by using the drag and drop option.
Step 1: Enter the one date on cell A2 as 12/Nov/2024.
Step 2: Now, in cell A3, apply the WORKDAY Function as shown in the below image.
The above formula takes the cell A2 as a reference and increases the date by 1.
Step 3: Drag the formula until cell A18.
Look at the formula here; 12/Nov/2024 is on Monday; we are increasing the day by 1. When we drag the formula, it will increment the date by 1 until 16/Nov/2024. If you drag one more time, it will jump to 19/Nov/2024 and excludes 17/Nov/2024 and 18/Nov/2024; those are weekends.
Similarly, in the next week, workdays are from 19/Nov/2024 to 23/Nov/2024, and weekends are 24/Nov/2024 and 25/Nov/2024.Example #2
The project starting date and project duration date calculate the project ending date by using a WORKDAY Function in Excel.
Note: No holidays apply to these projects.
Step 1: Copy and paste the above data to an excel sheet.
Step 2: Apply the WORKDAY Function in column C starting from cell C2.
Result is :Example #3
Consider the above example data for this also. But here, the list of holidays is available to estimate the project ending date.
The list of holidays are:
Apply the same formula as shown in example 2, but here you need to add one more parameter, i.e. holidays.
Result is :Example 2 vs Example 3:
Now we will see the difference between the two examples.
In the second example for the first project-ending date is 8/8/2024, and there is one holiday, for example, 3, so the ending date increased by 1 day.
For the second project, the ending date is 30/01/2024, for example, 2 and example 3; there 5 holidays, so the ending date increased by 7 days because of the in-between weekend.Example #4
Assume you are working in the Accounts Receivable team; you have a list of invoices and due dates against those invoices. You need to find the due days for those invoices.
Result is :
Initially, the result looks like serial numbers. We need to change our formatting to make it correct.
Step 1: Select the entire range.
Step 2: Now press ctrl +1. It will open up a formatting dialogue box.
Step 4: Your result looks like the below one.Things to Remember
If you want to use different weekends other than Saturday and Sunday, use chúng tôi function.
We can use only numeric values for the day’s argument.
The date and days should be accurate otherwise;, we will get the error as #VALUE!
If the date includes time, then the formula considers only the date portion and ignores the time portion.
If you supply decimal numbers, a formula will round down the value. For example: if you supply 10.6 days, then the formula treats this as 10 days only.Recommended Articles
This has been a guide to WORKDAY ID in Excel. Here we discuss the WORKDAY Formula in Excel and how to use the WORKDAY Function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –
In this tutorial, we’ll dive into the powerful Excel INDEX and MATCH functions, which are essential for manipulating and analyzing large sets of data.
We’ll start by exploring what these functions do and how they retrieve specific information from a table, and then we’ll write INDEX and MATCH formulas together as an alternative to the VLOOKUP formula.
We’ll also cover some practical use cases for INDEX and MATCH formulas.
Note: if you have Excel 2023 or later, or Microsoft 365 you should use the XLOOKUP function as this is easier and potentially more efficient.
Watch the INDEX and MATCH Video
Download the Excel File
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
Download the workbook. Note: This is a .xlsx file. Please ensure your browser doesn’t change the file extension on download.How the INDEX function works:
The INDEX function returns the value at the intersection of a column and a row.
The syntax for the INDEX function is:
=INDEX( the range of your table, the row number of the table that your data is in, the column number of the table that your data is in, and if your reference specifies two or more ranges (areas) then specify which area*)
*Typically only one area is specified so the area_num argument can be omitted. The examples below don’t require area_num.
INDEX will return the value that is in the cell at the intersection of the row and column you specify.
For example, looking at the table below in the range B17:F24 we can use INDEX to return the number of program views for Bat Man in the North region with a formula as follows:
The result returned is 91.
On its own the INDEX function is pretty inflexible because you have to hard key the row and column number, and that’s why it works better with the MATCH function.
Note: You may have noticed that the INDEX function works in a similar way to the OFFSET function, in fact you can often interchange them and achieve the same results.
How the MATCH function works:
The MATCH function finds the position of a value in a list. The list can either be in a row or a column.
The syntax for the MATCH function is:
Now I don’t want to go all syntaxy (real word 🙂 ) on you, but I’d like to point out some important features of the [match_type] argument:
The match_type argument specifies how Excel matches the lookup_value with values in lookup_array. You can choose from -1, 0 or 1 (1 is the default)
[match_type] is an optional argument, hence the square brackets. If you leave it out Excel will use the default of 1, which means it will find the largest value that is
0 will find the first value that is exactly equal to the lookup_value. The values in the lookup_array can be in any order.
Ok, that’s enough of the syntax.
In English and using the previous example:
find what row Bat Man is on,
in the column range B17:B24,
match it exactly (for this we'll use 0 as our argument))
The result is row 2.
We can also use MATCH to find the column number like this:
find what column North is in,
in the row range B17:F17,
match it exactly (again we'll use 0 as our argument))
The result is column 3.
So in summary, the INDEX function returns the value in the cell you specify, and the MATCH function tells you the column or row number for the value you are looking up.
INDEX MATCH Together:
The INDEX and MATCH functions are a popular alternative to the VLOOKUP. Even though I still prefer VLOOKUP as it’s more straight forward to use, there are certain things the INDEX + MATCH functions can do that VLOOKUP can’t. More on that later.
Using the above example data we’ll use the INDEX and MATCH functions to find the program views for Bat Man in the East region.
the range of your table,
replace this with a MATCH function to find the row number for Bat Man,
replace this with a MATCH function to find the column number for East)
The formula will read like this:
return the value in the table range B17:F24 in the cell that is at the intersection of, MATCH(
the row Bat Man is on) and, MATCH(
the column East is in)
The formula looks like this:
"Bat Man",$B$18:$B$24,0), MATCH(
So why would you put yourself through all that rigmarole when VLOOKUP can do the same job.
Reasons to use INDEX and MATCH rather than VLOOKUP
1) VLOOKUP can’t go left
Taking the table below, let’s say you wanted to find out what program was on the Krafty Kids channel.
VLOOKUP can’t do this because you’d be asking it to find Krafty Kids and then return the value in column B to the left, and VLOOKUP can only look to the right.
In comes INDEX and MATCH with a formula like this:
And you get the answer; ‘Mr Maker’.
Notice only the Programs column (B) was referenced in INDEX’s array argument? This means we can omit INDEX’s column number argument as there’s only one column in the INDEX array.
2) Two way lookup
The table below has a drop down list in B1 that enables me to choose the Sales Person from the table, and a drop down list in A2 for the region. In B2 I’ve got an INDEX + MATCH formula that returns the sales that match my two criteria.
Note: An alternative is to use a VLOOKUP and replace the hard keyed column number with a MATCH formula like this:Ways to improve these formulas:
1) Use named ranges instead of $C$33:$C$40 etc. to make formulas more intuitive and quicker to create.
2) An alternative to using a named range is to convert the data to an Excel Table whereby Excel automatically gives the table a named range.
3) If there is nothing else in the columns other than your table you could use column references like this C:C which will search the whole column.
Case Value_1 Code Block when Test_Expression = Value_1 Case Value_2 Code Block when Test_Expression = Value_2 Case Value_3 Code Block when Test_Expression = Value_3 Case Else Code Block when none of the case conditions are met Dim UserInput As Integer UserInput = InputBox(“Please enter a number between 1 and 5”) Select Case UserInput Case 1 MsgBox “You entered 1″ Case 2 MsgBox “You entered 2″ Case 3 MsgBox “You entered 3″ Case 4 MsgBox “You entered 4″ Case 5 MsgBox “You entered 5″ End Select Dim UserInput As Integer UserInput = InputBox(“Please enter a number”) Select Case UserInput Case Is < 100 MsgBox “You entered a number less than 100″ MsgBox “You entered a number more than (or equal to) 100″ End Select Dim UserInput As Integer UserInput = InputBox(“Please enter a number”) Select Case UserInput Case Is < 100 MsgBox “You entered a number less than 100″ Case Else MsgBox “You entered a number more than (or equal to) 100″ End Select Dim UserInput As Integer UserInput = InputBox(“Please enter a number between 1 and 100”) Select Case UserInput Case 1 To 25 MsgBox “You entered a number less than 25″ Case 26 To 50 MsgBox “You entered a number between 26 and 50″ Case 51 To 75 MsgBox “You entered a number between 51 and 75″ Case 75 To 100 MsgBox “You entered a number more than 75″ End Select Dim StudentMarks As Integer Dim FinalGrade As String StudentMarks = InputBox(“Enter Marks”) Select Case StudentMarks Case Is < 33 FinalGrade = “F” Case 33 To 50 FinalGrade = “E” Case 51 To 60 FinalGrade = “D” Case 60 To 70 FinalGrade = “C” Case 70 To 90 FinalGrade = “B” Case 90 To 100 FinalGrade = “A” End Select MsgBox “The Grade is ” & FinalGrade End Sub
The above code asks the user for the marks and based on it, shows a message box with the final grade.
In the above code, I have specified all the conditions – for marks 0 – 100.
Another way to use Select Case is to use a Case Else at the end. This is useful when you have accounted for all the conditions and then specify what to do when none of the conditions is met.
The below code is a variation of the Grade code with a minor change. In the end, it has a Case else statement, which will be executed when none of the above conditions are true.Sub CheckOddEven() Dim StudentMarks As Integer Dim FinalGrade As String StudentMarks = InputBox("Enter Marks") Select Case StudentMarks Case Is < 33 FinalGrade = "F" Case 33 To 50 FinalGrade = "E" Case 51 To 60 FinalGrade = "D" Case 60 To 70 FinalGrade = "C" Case 70 To 90 FinalGrade = "B" Case Else FinalGrade = "A" End Select MsgBox "The Grade is " & FinalGrade Dim FinalGrade As String Select Case StudentMarks Case Is < 33 FinalGrade = "F" Case 33 To 50 FinalGrade = "E" Case 51 To 60 FinalGrade = "D" Case 60 To 70 FinalGrade = "C" Case 70 To 90 FinalGrade = "B" Case Else FinalGrade = "A" End Select GetGrade = FinalGrade CheckValue = Range("A1").Value Select Case (CheckValue Mod 2) = 0 Case True MsgBox "The number is even" Case False MsgBox "The number is odd" End Select Select Case Weekday(Now) Case 1, 7 MsgBox "Today is a Weekend" Case Else MsgBox "Today is a Weekday" End Select Select Case Weekday(Now) Case 1, 7 Select Case Weekday(Now) Case 1 MsgBox "Today is Sunday" Case Else MsgBox "Today is Saturday" End Select Case Else MsgBox "Today is a Weekday" End Select Dim Department As String Department = InputBox("Enter Your Department Name") Select Case Department Case "Marketing" MsgBox "Please connect with Bob Raines for Onboarding" Case "Finance" MsgBox "Please connect with Patricia Cruz for Onboarding" Case "HR" MsgBox "Please connect with Oliver Rand for Onboarding" Case "Admin" MsgBox "Please connect with Helen Hume for Onboarding" Case Else MsgBox "Please connect with Tony Randall for Onboarding" End Select End Sub
Hope all the examples above were helpful in understanding the concept and application of Select Case in Excel VBA.
You May Also Like the Following VBA Tutorials:
Update the detailed information about Recover Damaged Excel Files With Recovery Toolbox For Excel on the Minhminhbmm.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!