You are reading the article Rows To Columns In Excel (Examples) updated in February 2024 on the website Kientrucdochoi.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 Rows To Columns In Excel (Examples)
Excel Rows to Columns (Table of Contents)
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & othersRows to Columns in Excel
In Microsoft Excel, we can change the rows to columns and columns to rows and vice versa, using TRANSPOSE. We can either use the Transpose or Transpose function to get the output.
The transpose function normally returns a transposed range of cells which is used to switch the rows to columns and columns to rows and vice versa, i.e., we can convert a vertical range of cells to a horizontal range of cells or a horizontal range of cells to a vertical range of cells in Excel.
For example, a horizontal range of cells is returned if a vertical range is entered, or a vertical range of cells is returned if a horizontal range of cells is entered.How to Convert Rows to Columns in Excel using Transpose?
It is very simple and easy. Let’s understand how to convert rows to columns in Excel using examples.
You can download this Convert Rows to Columns Excel?Template here – Convert Rows to Columns Excel?Template
Steps to use transpose:
Start the cell by selecting and copying an entire range of data.
Choose to paste special, and we will find the transpose button.
We will get the result converted to rows to columns.Example #1
Consider the below example where we have a revenue figure for sales month-wise. We can see that month data are row-wise and Part number data are column-wise.
If we want to convert the rows to columns in Excel, we can use the transpose function and apply it by following the below steps.
First, select the entire cells from A To G with data information.
Copy the entire data by pressing the Ctrl+ C Key.
Now select the new cells where exactly you need to have the data.
Choose the option paste special.
Select the fourth option in paste special, transpose, as shown in the below screenshot, highlighted in yellow.
In the above screenshot, we can see that rows have been changed to the column, and the column has been changed to rows in Excel. This way, we can easily convert the given data from row to column and column to row in Excel. For the end-user, if there is a huge amount of data, this transpose will be very useful, and it saves a lot of time instead of typing it, and we can avoid duplication.Example #2
In this example, we will convert rows to columns in Excel and see how to transpose the employee salary data by following the steps below.
Consider the above screenshot, which has the id number, emp name, HRA, Allowance, and Special Allowance. Suppose we need to convert the data column to rows; in these cases, the TRANSPOSE function will be very useful to convert it, which saves time instead of entering the data. We will see how to convert the column to a row with the below steps.
Once you copy the data, choose the new cell location.
We will get the paste special dialogue box.
Choose the Special Paste option.
Select the Transpose option in that, as shown below.
Once you have chosen the transpose option, the Excel row data will be converted to the column, as shown in the below result.
In the above screenshot, we can see the difference that the row has been converted to columns; in this way, we can easily use the transpose to convert horizontal to vertical and vertical to horizontal in Excel.
In Excel, a built-in function called Transpose Function converts rows to columns and vice versa; this function works like transpose. i.e., we can convert rows to columns or columns into rows and vice versa.
Syntax of Transpose Function:
Array: The range of cells to transpose.
When a set of an array is transposed, the first row is used as the first column of an array, and in the same way, the second row is used as the second column of a new one, and the third row is used as the third column of the array.
If we use the transpose function formula as an array, we have CTRL+SHIFT +ENTER to apply it.Example #3
In this example, we will see how to use the transpose function with an array with the example below.
Consider the below example, which shows weekly sales data, where we will convert the data to columns to row and row to column and vice versa using the transpose function.
Select the row you want to transpose.
Here we are going to convert the MONTH PLAN to the column.
We can see that there are 11 rows, so to use the transpose function, the rows and columns should be in equal cells; if we have 11 rows, then the transpose function needs the same 11 columns to convert it.
Choose exactly 11 columns, use the Transpose Formula, and select an array from C1 to C11, as shown in the below screenshot.
Now use CTRL+SHIFT +ENTER to apply as an array formula.
Once we use the CTRL+SHIFT +ENTER, we can see the open and close parenthesis in the formulation.
We will get the output where a row has been changed to a column, shown below.
Use the formula for the entire cells to get the exact result.
So the Final Output will be as below.Things to Remember about Convert Rows to Columns in Excel
The transpose function in Excel is one of the most useful functions, as it allows us to rotate the data without altering its information.
If any blank or empty cells exist, transpose will not work, giving the result zero.
While using the array formula in the transpose function, we cannot delete or edit the cells because all the data are connected with links, and Excel will throw an error message that “YOU CAN NOT CHANGE PART OF AN ARRAY.”Recommended Articles
This has been a guide to Rows to Columns in Excel. We discuss converting rows to columns in Excel using transpose, practical examples, and a downloadable Excel template. Transpose can help everyone to convert multiple rows to a column in Excel easily and quickly. You can also go through our other suggested articles –
You're reading Rows To Columns In Excel (Examples)
Inserting rows in Excel is one of the most basic stuff that we do almost everyday. And there are many ways to do this.
Now there could be various situations where you need to insert rows in Excel. For example, you may want to insert a single row, or multiple rows that are all together, or multiple rows that are not together.How to Insert Multiple Rows in Excel
In this tutorial, you’ll learn four different ways to do insert multiple rows in Excel.
Using Keyboard Shortcuts.
Using Insert Options.
Using Mouse + Keyboard (super trick).
Using Insert Copied Cells Option.
You can insert multiple rows in Excel using more than one keyboard shortcut.
Here is the first one:
Select the cell above which you want to insert multiple rows in Excel.
Press Shift + Space-bar to select the entire row.
Once the entire row is selected, press Control and hit the plus key (+) from the numeric keypad.
If you don’t have the numeric keypad, press Control and Shift and plus key (+) on the regular keyboard.
This will insert a single row right above the row that you selected.
Once you have inserted a row, you can repeat the same action by using the F4 key.
To do this, just select the row above which you want to insert a row and press F4. Hitting the F4 just repeats the last action.
What if you want to insert multiple rows in Excel at one go?
In the above example, we saw how to insert one row using the keyboard shortcut. If you want to insert multiple rows at one go, you can easily do it with a slight twist.
Here is how to do it:
It does not matter if the rows have data or are empty. This technique works in all cases.
Use the keyboard shortcut Control and Plus key (or Control and Shift and Plus key if you have a regular keyboard).
This will automatically enter the same number of rows as you selected to begin with. So if you selected four rows, it will insert four rows above it (as shown below):
Here is another keyboard shortcut that you can use:
Hold the ALT key and Press I followed by R.
You can also use the Insert functionality in Excel to insert multiple rows in Excel.
Here are the steps to use this:
Select the rows above which you want to insert the rows. For example, if you want to insert 4 rows between Row #3 and #4, select four rows staring from Row #4.
This will enter four rows above between Row #3 and #4 (as shown below):
You can also use the Insert Option available in the Home chúng tôi use it:
Select the rows above which you want to insert the rows.#3 Using Mouse + Keyboard Shortcut
This one is a super trick.
If you want to insert multiple rows above or below a row, here are the steps:
Select a row above or below which you want to insert rows.
You will notice a small green square at the end bottom right of the row number.
Hover your mouse on that green square and press Control + Shift on your keyboard. You will notice that the mouse icon changes from a plus to a double line icon with arrow on both sides.
This is one of the best ways to quickly insert any number of rows.
Another quick way to insert multiple rows in Excel is to copy a blank row and paste in where you want. Since you are copying a blank row, it would be the same as inserting a new row.
Here is how to do this:
Select a blank row and copy it.
Select the rows above which you want to insert the new rows. Select as many rows as you want to insert.
This will insert rows above the rows that you selected (as shown below):
These are four ways you can use to quickly insert multiple rows in Excel.
Ever find yourself staring at a heap of info in Excel, scratching your head about how to make it all neat and tidy? You’re in the right place! Today, we’re tackling one of Excel’s superpowers: sorting data alphabetically across multiple columns.
In this article, we’ll get our hands dirty with Excel and teach you how to sort data alphabetically across multiple columns. We’ll break down the basics of how Excel’s sorting function works, and guide you through the process step-by-step, using clear, easy-to-follow instructions.
So, let’s dive in and transform you into an Excel whiz!
Alright, before we dive into the nitty-gritty of multi-column sorting, let’s first take a moment to understand what Excel’s sorting function is all about.
Think of the sorting function as your own personal data organizer. It’s a feature that allows you to arrange your data in a particular order — and that’s super handy when dealing with tons of information. Excel’s sorting function is like having a personal librarian who knows exactly where each book should go.
When it comes to sorting, Excel gives you two options: ascending or descending order.
Ascending order means arranging the data from smallest to largest, or in the case of text, from A to Z.
Descending order means arranging the data from largest to smallest, or from Z to A for text.
But here’s where things get a bit more interesting. Excel doesn’t limit you to sorting just one column. It lets you sort multiple columns, which means you can arrange your data based on more than one criterion.
Imagine you’re dealing with a list of names, with separate columns for first name and last name. Excel allows you to sort the list by last name and then sort those with the same last names by first name. Pretty cool, right?
So, in a nutshell, Excel’s sorting function is a powerful tool that helps you make sense of your data. It organizes your information in a logical, easy-to-read manner, saving you time and effort.
Now that we’ve got the basics down, let’s move on to the fun part — learning how to sort data alphabetically in columns!
Before we jump into the deep end with multi-column sorting, let’s take a quick dip in the shallow end by sorting a single column in ascending order and then descending order.
To sort a single column in ascending order, follow the below steps:
Step 1: Open your Excel worksheet
Kick things off by opening the worksheet that has the raw data you want to sort.
Step 2: Select the data to be sorted
Step 3: Go to the Data tab and find the Sort and Filter group
If you want to sort a single column in descending order, you have to follow the same process. Here are the steps:
Step 1: Open your Excel worksheet
Kick things off by opening the worksheet that has the data you want to sort.
Step 2: Select the data to be sorted
Step 3: Go to the Data tab and find the Sort and Filter group.
And there you have it! You’ve just sorted a single column in ascending and descending order in Excel. Now, let’s take things up a notch and sort multiple columns.
Alright, now that you’ve mastered single-column sorting, it’s time to turn up the heat and tackle multiple columns. Don’t sweat it, though — if you’ve got the single column down, this is just a hop, skip, and jump away.
Step 1: Open your Excel worksheet
Start by opening the worksheet that holds all the data you want to sort.
Step 2: Select your data
Step 4: In the Sort dialog box, choose the first column you want to sort under the Sort by drop-down menu and specify the sorting order
Excel allows you to sort data in more ways than just alphabetically. You can also sort by color, by cell icon, or even by a custom list you’ve created.
For instance, if you have a column that uses cell color to indicate priority levels, you can sort your data based on those colors.
Simply go to the “Sort” dialog box, select the column, and then choose “Sort by Color” from the “Sort On” drop-down.
We’ve covered how to sort by more than one column, but you can also sort by more than one criterion within a single column.
For example, you might want to sort a column of text by cell color first and then alphabetically.
This can be done by adding two sort levels for the same column, each with a different “Sort On” criterion.
The SORT function in Excel can help you sort data based on multiple columns. Here is an example:=SORT(range, [sort_index1], [sort_order1], [sort_index2], [sort_order2], ...)
In this formula, you have to specify the following:
The data range
Columns to sort by (index)
Sort order (1 for ascending, -1 for descending)
You can add more columns to sort by adding more sort_index and sort_order pairs to the SORT function.
Data with merged cells can be tricky to sort. When you try to sort a range that includes merged cells, Excel will give you an error message.
But don’t panic, there’s a workaround.
You’ll need to unmerge all the cells, sort the data, and then reapply the merging. Just make sure to copy the merged cell’s value to all of the unmerged cells before sorting.
Next, we’ll troubleshoot some common issues you might run into when sorting Excel columns.
Even though Excel is a powerful tool, it’s not without its quirks. You might run into a few bumps along the road while sorting but don’t fret. We’re here to help you troubleshoot some of the most common issues.
Sometimes your data won’t sort correctly because it isn’t stored in the correct format. For instance, if numbers are stored as text, they might not sort numerically.
To apply a common cell format to your data, press Ctrl+1, go to the Number tab, and select the suitable format under “Category.”
If your data isn’t sorting as expected, check for extra spaces at the start or end of your cells. Excel includes these spaces in its sorting, which might throw off your results.
You can remove extra spaces using Excel’s TRIM function by typing “=TRIM(A1)” in a new cell (if A1 is the cell containing the leading space) and then copy the result to the original cell.
Also, make sure that there are no unexpected blank rows or cells in your dataset. Blank cells can affect your sorting process. If you find any blank cells, you can either delete the entire row or fill it with the correct data.
Before sorting your data, it’s a good idea to remove duplicate rows. This ensures that your sorted data is accurate and representative. Follow these steps to remove duplicates:
Excel will remove any duplicate rows and shows a message that how many duplicate rows were removed.
If Excel crashes or hangs during sorting, your worksheet might be too large, or your computer might not have enough resources. Try closing other applications to free up memory.
If your worksheet is large, consider breaking it down into smaller, more manageable chunks.
Remember, sorting can be a complex process, especially with large datasets. If you run into trouble, take a step back and try to troubleshoot one issue at a time. You’ve got this!
Now that you’ve gotten a handle on sorting in Excel, how about we sprinkle in a little extra magic?
To help you become an even more efficient data maestro, we’re going to share some additional tips and tricks that can make your sorting tasks quicker, smoother, and generally more awesome.
Keyboard shortcuts can save you time and enhance your productivity when working with Excel data. Here are a few shortcuts for sorting alphabetically:
Alt + A + S + A: Sort the selected data alphabetically from A to Z.
Alt + A + S + D: Sort the selected data alphabetically from Z to A.
Alt + D + S: Open the Sort dialog box to apply custom sorting.
Remember to select the desired data range or cell in the same row or column before using these keyboard shortcuts. Otherwise, you will get the wrong results.
Excel allows you to save your custom sort settings to reuse them later. Follow these steps to save your sorting options:
Step 1: Select your data range or the cells within the column you want to sort
Step 3: In the “Sort” dialog box, select the column name from the Sort by drop-down. Note that if there are no column headers, Excel is showing column letters.
Now your custom sort settings are saved, and you can see them in the Custom Lists section of the Order drop-down menu in the Sort dialog box for future use.
But remember, this is just the beginning. Excel is an incredibly powerful tool, with countless more features and functionalities waiting to be discovered.
This guide should have armed you with the skills and knowledge to tackle data sorting in Excel like a pro. But don’t stop here. The real magic happens when you take these lessons and apply them to your own work.
To learn more Excel tricks, check out the video below:
Histogram in Excel
Histogram Chart in Excel is a data analysis tool used to show the periodic rise and drop in the data with the help of vertical columns. We can find the Histogram chart option if we use Excel 2024. Still, for the older version of MS Excel, such as 2013 and 2010, we need to find this option in the Data Analysis option, which is available under the Data Analysis option.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & othersUses of Histogram Chart in Excel
A histogram is a graphical representation of the distribution of numerical data. A histogram is a column chart that shows the frequency of data in a certain range in a simpler way. It provides the visualization of numerical data by using the number of data points that fall within a specified range of values (also called “bins”).
A histogram chart in Excel is classified or made up of 5 parts:
Title: The title describes the information about the histogram.
X-axis: The X-axis is the grouped interval that shows the scale of values in which the measurements lie.
Y-axis: The Y-axis is the scale showing the times the values occurred within the intervals corresponding to the X-axis.
The bars: This parameter has a height and width. The height of the bar shows the number of times the values occurred within the interval. The width of the bars shows the interval or distance, or covered area.
Legend: This provides additional information about measurements.Why is the histogram chart important in Excel?
There are many benefits to using a Histogram chart in Excel.
The histogram chart shows the visual representation of data distribution.
A histogram chart displays a large amount of data and the occurrence of data values.
Easy to determine the median and data distribution.Types/Shapes of Histogram Chart
It depends on the distribution of data; the histogram can be of the following type:
A Bimodal Distribution
A Right Skewed Distribution
A Left Skewed Distribution
A Random Distribution
Now we will explain the shapes of the Histogram chart in Excel.Normal Distribution: A Bimodal Distribution:
There are two peaks. This is also called Double peaked distribution in this dist. Under this distribution in one data set, the results of two processes with different distributions are combined. The data is separated and analyzed like a normal distribution. This looks like the below image:A Right Skewed Distribution:
In this distribution, a large number of data values occur on the left side and a fewer number of data values on the right side. This is also called a positively skewed distribution. This distribution occurs when the data has a range boundary on the left-hand side of the histogram. For example, a boundary of 0. This dist. It looks like the below image:A Left Skewed Distribution:
This is also called a negatively skewed distribution. In this distribution, a large number of data values occur on the right side and a fewer number of data values on the left side. This distribution occurs when the data has a range boundary on the right-hand side of the histogram. For example, a boundary such as 100. This dist. It looks like the below image:A Random Distribution:
This is also called a multimodal distribution. In this dist., several processes with normal distributions are combined. This has several peaks; thus, the data should be separated and analyzed separately. This dist. It looks like the below image:Where is the Histogram Chart found in Excel?
The histogram chart option is found under Analysis ToolPak. The Analysis ToolPak is a Microsoft Excel data analysis add-in. This add-in is not loaded automatically on Excel. Before using this, we need to load it first.Steps to load the Analysis ToolPak add-in:
This will open the below Add-Ins dialog box.
This will open an Add-Ins dialog box.
The Analysis ToolPak is loaded in excel now and will be available under the DATA tab with the name Data Analysis.How to Create a Histogram Chart in Excel?
Before creating a histogram chart in Excel, we must create the bins in a separate column.
Bins represent the intervals into which we want to group the data set. These intervals should be consecutive, non-overlapping, and of equal size.
There are two ways to create a histogram chart in Excel:
If you are working on Excel 2024, there is a built-in histogram chart option.
If you are working on Excel 2013, 2010, or earlier versions, you can create a histogram using Data Analysis ToolPak.Creating a Histogram chart in Excel 2024:
Excel 2024 adds a histogram chart option as an inbuilt chart under the chart section.
Select the entire dataset.
The histogram chart would appear based on your dataset.Creating a Histogram chart in Excel 2013, 2010, or earlier version:
Download the Data Analysis ToolPak as shown in the above steps. You also can activate this ToolPak in Excel 2024 version too.Examples of Histogram Chart in Excel
You can download this Histogram Chart Excel Template here – Histogram Chart Excel TemplateExample #1
Let’s create a dataset of scores (out of 100) of 30 students as shown below:
For creating a histogram, we need to create the intervals at which we want to find the frequency. These intervals are called bins.
Below are the bins or score intervals for the above data set.
Please follow the below steps to create the Histogram chart in Excel:
A Histogram dialog box will open.
In the Histogram dialog box, we will enter the following details:
Select the Input Range (as per our example – with the scores column B)
Select the Bin Range ( Intervals column D)
This would create a Frequency Distribution table and the Histogram chart in the specified cell address.
There are below points that need to keep in mind while creating Bin’s or Intervals:
The First bin includes all the values below it. For bin 30, frequency 5 includes all the scores below 30.
The last bin is 90. This bin includes any data values which are higher than the last bin. In our example, 3 values are higher than the last bin, 90. If the values are higher than the last bin, Excel automatically creates another bin – More.
This chart is called a static histogram chart. If you want to change the source data, you must create the histogram again.
You can do the formatting of this chart like other charts.Example #2
Let’s take another example with 15 data points which are the salary of a company.
Now we will create the bins for the above dataset.
For creating the histogram chart in Excel, we will follow the same steps as in example 1.
It will open a histogram dialog box.
Select the Input Range with the Salary data points.
Select the Bin Range with the bins column.
The below chart will appear as follows:
The First bin, $25,000, will include all data points less than this value.
The last bin, More, will include all the data points over $65,000. It’s created automatically by Excel.Pros
The histogram is very useful while working with a large amount of data.
A histogram chart shows the data in a graphical form that is easy to compare and understand.Cons
Histogram chart is very difficult to extract the data from the input field in the histogram. It means difficult to point out the exact number.
While working with histograms, it creates a problem with multiple categories.Things to Remember About Histogram Charts in Excel
A histogram chart uses for continuous data and determines the range of data with the bin.
The bins are usually determined as consecutive and non-overlapping intervals.
The bins must be adjacent and are of equal size (but are not required to be).
If you are working with Excel 2013, 2010, or earlier versions, you must activate the Excel Add-Ins for Data Analysis ToolPak.Recommended Articles
This has been a guide to histograms in Excel. Here we discuss its types and how to create a Histogram chart in Excel, along with Excel examples and a downloadable Excel template. You may also look at these useful charts in Excel –
XIRR Function in Excel
XIRR function is also one of the financial functions in Excel, which is used to calculate the Internal Rate of Return (IRR) in any series of intervals. XIRR function works when we assign the dates of obtained intervals, whether regular or irregular and calculates the internal rate of return on that array of cash flow. The good thing about XIRR Function is it considers only irregular series of cash flow or installment intervals. In contrast, most of the functions in Excel give fine results when a regular interval series is selected.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
XIRR Formula in Excel
Below is the XIRR Formula.How to Open XIRR Function?
We Get A New Function Windows Showing In Below Mention Picture.
Then we have to enter the details of cash value and dates.
The shortcut of using the XIRR Formula
Values (Required Argument) –The values representing the series of cash flows. Instead of an array, it can reference a range of cells containing values.
Dates (Required Argument) – A series of dates corresponding to the given values. Subsequent dates should be later than the first date as the first date is the start date, and subsequent dates are future dates of outgoing payments or income.
Guess (Optional Argument) –It is an initial guess of the IRR. If omitted, Excel takes the default value, which is 10%.
The format of the result
It is the internal interest rate, so we consider it in the percent (%) number format.How to use the XIRR Function in Excel?
This XIRR function is very simple and easy to use. Let us now see how to use XIRR in Excel with the help of some examples.
You can download this XIRR function Excel Template here – XIRR function Excel TemplateExample #1
Calculating XIRR value using XIRR Formula =XIRR(H3: H9, G3: G9)
The answer will be 4.89%
In the above table, the interest inflows are irregular. Hence, you can use the XIRR function in Excel to compute
The internal rate of interest on these cash flows, the investment amount is showing a minus sign. When you get the result, then change the format with %.Example #2
Calculating XIRR value using XIRR Formula =XIRR(A3: A15, B3: B15)
The answer will be 16.60%
In an Excel sheet, first, enter the original amount invested. A ‘minus sign should represent the amount invested. In the following cells, enter the returns received during each period. “Remember to include the ‘minus’ sign whenever you invest the money.
Now, find XIRR using the values that refer to a series of cash flows corresponding to a schedule of payments in dates. The series of values must contain at least one positive and one negative value. The first payment refers to the investment made at the beginning of the investment period and must be a negative value. All succeeding payments are discounted based on a 365-day-year.
The date is when the first investment and the returns were received. Each date should correspond to its respective investment made or income received, as shown in the above table. Dates should be entered in a ‘DD-MM-YY (date-month-year)’ format, as errors can occur if the format is not followed. If any number in the dates is invalid, or the format of dates is inconsistent, XIRR will reflect the “#value!” Error.
If we discuss error problems, this is also an important part. If our data is not correct, we will face such a type of issue as below mention.
#Num! Occurs if either:
The supplied values and date arrays have different lengths.
The supplied values array does not contain at least one negative and at least one positive value;
Any of the supplied dates proceeds to the first supplied date.
The calculation needs to converge after 100 iterations.
#value! : – occurs if any supplied dates need to be recognized as valid Excel dates.
If you attempt to input dates in text format, there is a risk that Excel may misinterpret them, depending on the date system or data interpretation settings on your computer.Recommended Articles
This has been a guide to XIRR Function. Here we discuss the XIRR Formula and how to use XIRR Function along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –
Introduction to LOOKUP Function in Excel
For example, if you want to search for the number 5 in a table, you could use the Lookup function to return the other information in the same row or column as 5. The LOOKUP function is a built-in worksheet function in Microsoft Excel. It is available in Lookup & References under the Formula tab.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
Syntax of Excel LOOKUP Function, The LOOKUP function in Excel is of two types: Vector and Array.1. Vector form LOOKUP in Excel
The vector form of the LOOKUP function is useful for searching for a specific value in one row or column and returning a value from the same location in another row or column. For instance, you have a list of students with their names in one column and grades in another, and you want to find a grade for a particular student. The vector form of the LOOKUP function in Excel will search the student’s name and then display their grade from the same row.
If you know the row or column range where the search value is present, you can use this form of the LOOKUP function to get the value from that row or column.
Syntax: =LOOKUP(lookup_value, lookup_vector, [result_vector])
Lookup Value: (Required) It is the value that we want to search in one row or one column. It can be a text, number, cell reference, value, or name.
Lookup Vector: (Required) The range of one row or column where the lookup_value will be first searched. The value can be numbers, text, or reference values.
Result Vector: (Optional) It is the range of a single row or column from where we want to fetch the required value. The size of the result_vector must be the same as that of the lookup vector.2. Array form LOOKUP in Excel
An array is a combination of values in rows and columns in the form of a table. The array form of the LOOKUP function searches for a specific value in the first row or column and returns a value from the same region in the last row or column of the array. Use this array form only when the values you want to search are in the array’s first row or column.
Note: Vlookup & Hlookup is used instead of an array form because it has limited options
Syntax =LOOKUP(lookup_value, array)
Lookup _value: (Required) lookup_value in array form is the value the LOOKUP function searches for in an array.
Array: (Required)It is the range of cells of multiple rows and columns, like a two-dimensional data (table), where you want to search the lookup_value.Let’s Learn the Use of the LOOKUP Function in Excel.
In the following section, you will learn how to use the Lookup function with the help of various examples. You will also learn to create a LOOKUP formula to find a value for specific criteria.
You can download this LOOKUP Function Excel Template here – LOOKUP Function Excel TemplateExample #1
The result, “Clothes,” is displaced. Thus, Kim sold Clothes.
This LOOKUP function searches the lookup_value, Cell B8 (Kim), in the lookup_vector, A2:A6, and returns the value in the same row from the result_vector, B2:B6. In simple terms, the value of Cell B8 is present in A4, and the function returns the value from the same position in another column, B4. The final output is Clothes.Example #2
Let’s take another example to understand the vector form of the LOOKUP function.
Output 70 is displayed as shown below. Hence, the price of ID A04 is 70.
Explanation of the formula: “=LOOKUP(B8,A2:A6,C2:C6)”
The lookup_value, A04, is first searched in the range of lookup_vector (A2 to A6). Here the lookup_value is A04; the lookup_vector is A2:A6, and the result_vector is C2:C6. The look_up value is in A5; now, the function returns the value in the same row from the result_vector range (B2:B6), i.e., B5. Thus, the output of the given formula is 70.Example #3
Output 20 is displaced in Cell B7. Thus, the quantity of apples is 20.Example #4
The result of the formula is 60. Thus, the price of the product sold by Doug is 60.
Explanation of the formula: “=LOOKUP(B8, A1:C6)”
Here, the lookup_value is B8, and the array range is A1: C6. The formula will search for the value of Cell B8 from Cell A1 to Cell C6 and returns the value corresponding to Cell B8.Example #5
The syntax for retrieving the last value of a specific column is:Things to Remember 1. While Using the Vector Form of the LOOKUP Function
The LOOKUP function with a result_vector provided will check for the lookup_value in the lookup_vector range and return the corresponding value from the result_vector.
Without a result_vector, it will return the result value found from the lookup_vector.
If the lookup_value cannot be found, the function returns the next smallest value or equal to the lookup_value.
If the lookup_value is less than the lowest value in the lookup_vector, the function will display the #N/A error.
The function will return the last value in the range of the lookup value that exceeds each vector value.
If it cannot find the exact lookup_value, it searches for the next highest value that is lower than or equal to the lookup_value.
Let us learn about the above conditions through an example. The below data contains details of employee codes and their name.
Note: The data should be in ascending order.
=LOOKUP(1150,A3:A7,B3:B7) The Lookup function finds the exact match of the lookup_value in column A. Elizabeth
=LOOKUP(1156, A3:A7, B3:B7) The function looks for the nearest value for 1156 and returns the value of 1157 John
=LOOKUP(1151, A3:A7, B3:B7) The function looks for the nearest value for 1151 and returns the value of 1150 Elizabeth
=LOOKUP(1124,A3:A7,B3:B7) lookup_value is smaller than all values present in A3:A7. #N/A
=LOOKUP(0, A3:A7, B3:B7) The function looks for 0 and returns an error because 0 is less than the smallest value, 1150. #N/A
=LOOKUP(1162, A3:A7, B3:B7) The function looks up for value 1162 and returns the last value from column A. Mary2. While Using the Array Form of the LOOKUP Function
If the array range has multiple columns than rows (broader than taller), then the LOOKUP function will look for the lookup_value in the first row of the array (like HLOOKUP).
If the array range has multiple rows than columns (taller than broader) or an equal number of rows and columns, then the LOOKUP function will look for the lookup_value in the first column (like VLOOKUP).
In both cases, the LOOKUP function will return the very last value in the row or column of the array.Essential Notes
Sort the values in the lookup vector and result vector in ascending order, e., from A to Z if the values are in text and smallest to biggest if they are in numeric form.
The LOOKUP function in Excel may provide incorrect results or errors if the data is unsorted.
The LOOKUP function is not case-sensitive. It does not distinguish between text written in uppercase and lowercase.
The lookup_vector and result_vector should be the same size when using the vector form of the LOOKUP function.Frequently Asked Questions(FAQs)
Answer: There are two types of lookup formulas in Excel.
Vector form: The formula for the vector form of the lookup function is “=LOOKUP(lookup_value, lookup_vector, [result_vector]).”
Array form: The formula for the array form of the lookup function is “=LOOKUP(lookup_value, array).”Recommended Articles
This article is a tutorial for the LOOKUP Function in Excel. We have covered how to use the LOOKUP function for different conditions in Excel, practical examples, and a downloadable Excel template. You might also read our other recommended articles:
Update the detailed information about Rows To Columns In Excel (Examples) on the Kientrucdochoi.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!