Jul 13, 2016 This video is to demonstrate how to use a vlookup function using both relative and absolute referencing. Dec 3, 2018 - In this tutorial, you learn how to use VLOOKUP in Excel in 6 (easy). (or around) the F2 and pressing the shortcut F4 (on MAC: Command + T).
I have a workbook with a VLookup page 'Rules' that references table array 'Pricing' on another sheet within the workbook. I want to duplicate the pair to make a second set within the same worksheet: 'Rules2' and array 'Pricing2'. However, when I copy the tabs and try to link Rules2 to Pricing2 it returns 'N/A'. The new tabs are an exact match to the originals, with the exception that the numbers in Pricing2 are different. Here's my original formula that works in 'Rules': =VLOOKUP($B2,Pricing!$B:$P,2,FALSE) But this formula returns N/A in 'Rules2': =VLOOKUP($B2,Pricing2!$B:$P,2,FALSE) I know it is seeing the tab, because if I change the name of the tab it changes in the formula automatically.
This lesson shows you how to write formulas using INDEX and MATCH to let you perform lookups that VLOOKUP can't, and which run much faster on large spreadsheets than VLOOKUP. This lesson explains how INDEX and MATCH work on their own, and then shows you how to write an INDEX MATCH formula that can look left as well as right, and performs much faster than VLOOKUP on large tables. INDEX MATCH works very well if your lookup data is not in the first column, or you want to look to the left of the lookup data, rather than to the right (which is all VLOOKUP can do). What you'll learn in this lesson In this tutorial, you'll learn:.
How to use the MATCH function. How to use the INDEX function. Scenarios where VLOOKUP doesn't work or isn't the best solution. How to combine the INDEX and MATCH functions in your formulas to perform lookups that VLOOKUP can't. Why INDEX MATCH is so much better than VLOOKUP? For many users, VLOOKUP just does what they need. But there are three scenarios where VLOOKUP falls short.
That's where INDEX MATCH comes in handy:. VLOOKUP can only look from left to right. You look a value in one column in a table, and then return a value from a column to the right. But what happens if you want to look from right to left? VLOOKUP simply can't do that.
INDEX/MATCH can. VLOOKUP is prone to error, especially in big spreadsheets. With VLOOKUP, you specify a number to represent which column you want to return the value from. So you have count the columns to figure out which one you want. Hopefully you'll notice if you get it wrong but what if you don't? Finding and debugging this error can be a nightmare. With INDEX MATCH, you select the specific column of data from which you want to return the value.
That's much more reliable and easier to debug when things go wrong. VLOOKUP slows down big spreadsheets.
When the value you are looking up is in one column, and the value you want to return is in the 30th column in the table, VLOOKUP requires you to select all 30 columns. This results in a lot of extra computations and can bring large spreadsheets to their knees. With INDEX/MATCH, you select the column containing the lookup value and the column containing the return value, and nothing else. That makes INDEX/MATCH much more efficient.
Recap of what the INDEX function does. The INDEX function has a very simple job - look in a column of data and return a value from the row that you specify. Here's an example of the INDEX function in action: =INDEX(A2:A8,3) In this example, we tell Excel to return the 3rd value in the range A2:A20. Here's what that looks like: Note that I've changed the formula from above to use the value in B11 as the row to return, rather than typing 3 directly into the INDEX function. You can read more about the INDEX function in these lessons:. Both links open in a new tab. Recap of what the MATCH function does The MATCH function also has a simple job - look in a column of data and return the row number of a value that you specify.
It's a bit more complicated than the INDEX function, but we'll use it in a simple way in this lesson. You can find out more about the MATCH function in this lesson: (opens in a new tab).
Here's an example of the MATCH function in action. Let's assume we have a column of names, and we want to know which row Birgit is found in. We'll assume that each name appears only once in the list - this will make things simple for us. =MATCH('Birgit',B2:B8,0) Here's a worked example to illustrate the use of the MATCH function: As you'll see, I've adjusted the example to use the value in B11 as the value to look up, rather than typing 'Birgit' directly into the formula. Note that the third parameter in the formula is the matchtype. By entering zero as the last parameter in the formula, we tell Excel we are looking for an exact match. The VLOOKUP function has a similar parameter, but the MATCH function uses this parameter differently.
I won't go into the differences here, but if you're planning to use the MATCH function a lot, you should read our lesson on how to which covers the matchtype parameter in detail. Putting INDEX and MATCH together Now that we understand what the INDEX and MATCH functions do on their own, let's put them together to create an INDEX MATCH formula that can replace VLOOKUP - and do much more.
Example 1 - Use INDEX MATCH to replace VLOOKUP Let's start with a simple example, and use INDEX/MATCH to do something that VLOOKUP can do - find the name of the person from France. First, here's the VLOOKUP function you would use to do this: =VLOOKUP('France',A2:B8,2,0) This formula says 'Look for France in column A and if you find it, return the value from the second column in the same row. If you don't find France, return an error' Here's how we can do this with INDEX/MATCH: =INDEX(B2:B8,MATCH('France',A2:A8,0)) This formula says 'Find the row that contains France in column A, and then get the value in that row in column B. If you don't find France, then return an error'.
Here's our example with this formula combining INDEX and MATCH: Once again, note that I'm using B11 in my formula rather than typing 'France' directly into the formula. Example 2 - Using INDEX MATCH to look from right to left. Obviously, when compared to the VLOOKUP version of the formula in the example above, using INDEX MATCH results in a longer formula. For that reason, you may prefer to keep using VLOOKUP rather than INDEX/MATCH for simple examples. However, what if we want to do a lookup from right to left, instead of left to right?
For example, what if we know the person's name, but want to find out what country they are from? Let's now look at how we can use INDEX MATCH to do a lookup from left to right, and find out the country a person is from.
Here's an updated version of the previous example: Unsurprisingly, the formula is almost unchanged. We're still finding an item in a list (Melodie) and then retrieving the value in the same row from another column (France). Why INDEX MATCH is faster than VLOOKUP At the start, I stated that INDEX MATCH is faster than VLOOKUP. The reason this is so isn't immediately obvious with the simple examples provided here.
However, consider this scenario:. Our INDEX MATCH formula in Example 1 looks like this:. =INDEX(B2:B8,MATCH('France',A2:A8,0)). As you can see, this formula specifies the lookup column (A2:A8) and the value column (B2:B8), whereas the VLOOKUP function doesn't explicitly specify the value column - it simply gives a number that defines where the value column is relative to the lookup column:. =VLOOKUP('France',A2:B8, 2,0). But what if our value column were in row AX (column 50)? Here is how our two formulas would look:.
=VLOOKUP('France',A2:B2,50). =INDEX(AX2:AX8,MATCH('France',A2:A8,0)). Our VLOOKUP formula is going to have to count across 50 columns to find the lookup value - this is going to take time, particularly if you have a spreadsheet with hundreds or thousands of rows, each containing this VLOOKUP formula. If you've worked with a spreadsheet like that you'll be aware that it can take several seconds to recalculate the spreadsheet. By comparison, our INDEX function already knows where it needs to look, so it is able to complete its task a lot faster than VLOOKUP. This is good for your productivity since you don't have to wait for the recalculation each time you change a value in the spreadsheet. Add to that the fact that our INDEX function is much easier to understand by looking at it - and troubleshoot - than the VLOOKUP formula, and you'll see that INDEX MATCH is also less prone to errors.
Summary - using INDEX MATCH as an alternative to VLOOKUP In this lesson, we've learnt why VLOOKUP isn't always the best choice when trying to find values in a table based on a lookup value. We've looked at an example where INDEX MATCH can do something VLOOKUP can't do (look to the left in a table as well as the right).
We also talked about how much more efficient and less error-prone the INDEX MATCH combination can be compared to VLOOKUP. Hopefully this lesson has helped you understand how to combine the INDEX MATCH functions. If you have any feedback on this lesson, or specific questions in relation to the use of INDEX MATCH, feel free ask them in the comments below.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours.
Hi Bingying It depends on how you want to determine which column to choose. In the following example, I've written a formula that will look at the value in C12 (City), find the column which contains 'City' in the first row, and then modify the INDEX/MATCH formula to look in that column. Here's the formula so you can copy it and paste it into your own spreadsheet. I've also updated the =INDEX(INDIRECT(ADDRESS(ROW(A2),MATCH(C12,A1:C1),4)&':'&ADDRESS(ROW(A8),MATCH(C12,A1:C1),4)),MATCH(C11,C2:C8,0)) The formula does several things differently to the other examples in this lesson:. Takes the value in C12 and finds it in the column headings (A1:C1) using the MATCH function. It has to do this twice to find the start and end of the range that the INDEX function will look in.
Constructs the cell range for that column. In this example, with City in C12, the cell range will be B2:B8. Note that the formula uses row 2 and row 8 for the lookup range - this is hard coded so you'd need to modify it for your own spreadsheets. The value '4', which appears twice in the formula, is used to define the range using relative references (B2:B8) rather than absolute or mixed references (e.g. Uses the INDIRECT function to turn the cell range (B2:B8) from a text string into an actual cell range that Excel can use. Note that the formula returns an error if C12 is blank. You could modify this approach to specify the column number directly rather than using the MATCH function to find the column which contains the value in C12.
Here's a version of the formula that would work if C12 contained the column number rather than the column heading: =INDEX(INDIRECT(ADDRESS(ROW(A2),C12,4)&':'&ADDRESS(ROW(A8),C12,4)),MATCH(C11,C2:C8,0)) You could then use any method you like to decide which column number to use in the main INDEX/MATCH formula. I hope that helps. Regards David. Submitted by Steve on Sat, - 18:01. Hi Juli Comparing your VLOOKUP formula and INDEX/MATCH functions, it looks like they are doing completely different things. The lookup value in your VLOOKUP function is A4, whereas it is B3 in your INDEX/MATCH function.
Your VLOOKUP function looks for a matching value in column C. The INDEX/MATCH function looks in the range A3:A86.
Your MATCH function is trying to look in more than one column. MATCH can only look at one column.
Here's the INDEX/MATCH equivalent of your VLOOKUP function: VLOOKUP: =VLOOKUP(A4,LKUP!C:E,3,FALSE) INDEX/MATCH equivalent: =INDEX(LKUP!C:C,MATCH(A4,LKUP!E:E,0)) One further comment is that I would be wary of using whole column references (E:E, C:C) when writing an INDEX/MATCH formula. The reason is that there are more than a million rows in an Excel worksheet, and MATCH will return the row number of the first row that contains a matching value. However, you've told Excel to take the result of the match function and look in the range A3:A86. If the row number where the lookup value was found is not between 3 and 86, your INDEX function will cause an error. I hope this helps Regards David. Submitted by J.A.Padmanabhan on Wed, - 03:20.
I was trying to rank the students and listing out the names of toppers. I used the three functions index, match and large,1 large2 and so on. The problems occurs when there is a tie between two students. It returns the value that occurs first and does not get the next name. You can find the name of Viki Malhotra repeated twice and the name of Nagesh Majhi does not appear. Can anyone help out tracking out a modification to the formula I have used??? Submitted by Betsy on Fri, - 12:32.
Is there any way to search a spreadsheet for location of a word and then set the lookup range based on the location of that word? I have a spreadsheet with Multiple Department Categories and the same sub-category within each main category ( example below and spreadsheet attached). Every day I must generate this report and pull out only some of the data. Each day, the report is a little different, things are never in the exact same row since each day's activity is a little different.
Some days, the range of cells underneath each main Dept. Category are bigger than other days. I am using vlookup and it is only accurate some of the time since the lookup ranges keep changing and sometimes it returns a number from the wrong Department Category since it is only looking for the sub-category name. Hi Betsy It sounds to me like a Pivot Table would do most of what you need. You would need your spreadsheet organised so that your data was in three columns: Department, Category, Value (although the sequence of the categories won't matter) You would need to make sure there was a value in every cell in every row (or that you at least have a value in every row for Department and Category). That lesson covers most of what you need to know to solve the problem as I've described. Let me know if that helps, and feel free to post back here (or in the comments for the Pivot Table lesson) if you still need help.
Regards David. Submitted by Daniel Pilott on Sun, - 04:42. Hi, many thanks for the index and match tutorial its been a great help to me.
I am struggling with another problem now and was wondering if you had any ideas? I have a table with 3 columns, colour, cost and code I want to enter a colour into a cell and then return a list of the 10 lowest costs and corresponding codes for that colour.
I was thinking this could be done with a combo of index,match and small commands but I can't work it out. My sheet is attached, thanks in advance for any help.
Submitted by Ketan on Thu, - 03:30. I have 2 EXCEL files: the one included in my comment and a second one (CLI.XLSX) containing 2 columns: cliid, cip I need to update the cliid column of the CA.XLSX file thanks to the cip column which is common to both files (value contained in this column is unique). My efforts to introduce the link method into the INDEX MATCH formula are unsuccessfull! An example of formula into cliid of CA.XLSX would be appreciated! Thank you in advance for your help.
Submitted by Hossana Baurer on Thu, - 02:54. WITH VLOOKUP, TO RETURN THE VALUE FOR A MONTH, I USE A RANGE FOR THE MONTH ( = - 2 COLUMNS). HOW DO I USE 2 COLUMNS FOR THE MATCH IN INDEX MATCH. FOR EXAMPLE, HERE IS MY TABLE. WHEN I SHOW A DATE OF, I EXPECT A RETURN OF 1. THIS IS NOT WORKING FOR INDEX MATCH.
WHAT DO I NEED TO DO? OR DOES INDEX MATCH NOT DEAL WITH MORE THAN ONE COLUMN IN THE MATCH? Date Date Month 1 2 3 4 5 6 7 8 9 10 11 12 SO TO RETURN 1 FOR JANUARY I WOULD USE FORMULA =IF(A43=',',VLOOKUP(A43,ARGUMENTS!$B$4:$D$15,3)). Submitted by William Cobb on Tue, - 01:36. Good evening, I am trying to make an excel document where my Marines can enter in their rank and years of service. Once they do this a formula will be able to auto search a column Q1:AQ1 (rank) against a row P2:P23 (years in service) once it matches them it will search an a field for their pay amount (think a times table chart). My current formula of (below) is coming up as #REF!
=INDEX(Q2:AQ23,MATCH(A2,Q1:AQ1,FALSE),MATCH(A4,P2:P23,FALSE)) The end result will be an auto calculation for the new DOD retirement. Any help would be greatly appreciated. Hello, I am currently using excel to keep track of my financial records. It's the end of the year and I'm getting ready for taxes. I would like to take all of my information from the spreadsheet containing my check register, and put it into another sheet that will break down my income and expenses by their designated categories and by whom the transaction was made with. I have uploaded the file I am working on to this comment. Is there a formula or multiple formulas that I can use for this task?
This is the formula that I tried to use to add all of the corresponding cell contents together from payments made to Mile Saver Shopper. =SUMIF('Check Register'!' Check Register'!$H4:$L740,'Total Spending'!$B10,'Check Register'!$K:$L) and it did not work. Obviously wasn't correct. Thanks for any help! Submitted by Neale Shemilt on Thu, - 19:42. How do I add a value from one sheet to another?
Sheet A has a large database, and one of the categories have no phone numbers. Sheet B had that category with the phone numbers. I want to get the phone number that matches the company inserted eg ABC co in Sheet B has phone number 1234, we want to find ABC co in Sheet A, and add the phone number to that line, so in Sheet A, ABC co now shows the phone number 1234 What is the formula to do this? Submitted by vylance tiong on Sat, - 06:22.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |