128
Points
Questions
4
Answers
28

Hi Veenie,
Could you please share the file as an attachment else send mail to the following
excelrocker@ymail.com
 2381 views
 1 answers
 0 votes

If you’re using Excel and you’ve already learned how to use INDEX MATCH, you’re well on your way to becoming proficient with Excel lookups. What INDEX MATCH MATCH offers you is a more powerful version of the formula. Instead of just a vertical lookup, INDEX MATCH MATCH allows you to perform a matrix lookup, which is also known as a twoway lookup. This combination formula may initially seem complex because of its three individual formulas, but after you understand each component and how they interact, using this tool will become second nature to you. INDEX MATCH MATCH is one of several lookup formulas you should learn to become adept in database theory.
Objective and When to Use
There’s really just one key condition that needs to be met before you can use INDEX MATCH MATCH:
A matrix lookup can only work if your data table has lookup values on both the top and left hand side.
Basically, your data needs to be in a matrix format. People usually create matrixes, with lookup values both vertically and horizontally, to cross reference two different fields. In the example below, we are cross referencing the field State with the field Year and showing the relevant data point for Sales.
Creating a matrix saves you space within your spreadsheet and is more visually appealing. However, most data sets are not organized in this fashion. In fact, if you follow properdatabase theory, your data actually should not have lookup values going both vertically and across your table. A properly formatted table would look like the example below:
Before moving forward, ensure that you are using the proper formula for your data set. There are several other lookup options you can choose from if your data does not fit the requirements for INDEX MATCH MATCH. For example, if you only have lookup values on the top of your data set, you should consider using HLOOKUP. If you only have lookup values on the very left hand column of your data set, you should consider using VLOOKUP or INDEX MATCH.
The Syntax
Below is the syntax for using this formula combination. Don’t worry if it doesn’t make sense now; the rest of this post will provide context for each component and we’ll review a more practical version of the syntax that’s easier to remember.
= INDEX ( array , MATCH ( lookup_value , lookup_array , 0 ) , MATCH (lookup_value , lookup_array , 0 ) )
Not surprisingly, INDEX MATCH MATCH is based on the INDEX and MATCH formulas, which we will now go through in detail.
The INDEX Formula
The INDEX formula asks you to specify a reference within a range and returns a value. In its simplest form, you just indicate either a row or column as your range, specify a reference point, and the value that matches that reference point is returned. For example, if we were to select the left hand column of this table, and specify the reference “6”, the INDEX formula would return the value “WA”.
Now instead of using just selecting a single row or column, what you can also do with the INDEX formula is select an entire matrix, with multiple rows and columns, as your array. The key difference here is that, instead of just specifying a single appearance order as a reference, you must now provide both a vertical and horizontal reference to return your value. (Please note that the INDEX formula always takes the vertical reference first) Using the INDEX formula with a matrix reference represents the foundation of utilizing INDEX MATCH MATCH. The syntax for the INDEX formula by itself is as follows:
= INDEX ( array , row_number , column_number )
For example, let’s say we selected the entire sales data table, and then specified “6” as the row number and “4” as the column number. The INDEX formula performs the intuitive action of going down 6 rows and over 4 columns with the range we selected to return the value of “$261.04”.
The MATCH Formula
The MATCH formula asks you to specify a value within a range and returns a reference. The MATCH formula is basically the reverse of the INDEX formula. The two formulas have the exact same components, but the inputs and outputs are rearranged.
= MATCH ( lookup_value , lookup_array , 0 )
To give you an example of the MATCH formula, if we were to select the entire left hand column and then specify “WA” as our lookup value, the MATCH formula would return the number “6”. Please note that you have to put in a “0” as the last argument to ensure that the MATCH formula looks for an exact match.
How it Works
As mentioned before, when using the INDEX formula across a matrix it requires both a horizontal and vertical reference. The only additional complexity that INDEX MATCH MATCH adds is that the vertical and horizontal references are turned into MATCH formulas.
Putting it Together
Below is a simplified version of the syntax describing the inputs with the appropriate context for our goal. In case you get lost in the individual steps, you can always refer back to this notation.
= INDEX ( entire matrix , MATCH ( vertical lookup value, entire left hand lookup column , 0 ) , MATCH ( horizontal lookup value , entire top header row , 0 ) )
Step 1: Start writing your INDEX formula and select the entire table as your array
Step 2: When you get to the row number entry, input the MATCH formula and select your vertical lookup value for the lookup value input
Step 3: For the lookup array, select the entire left hand lookup column; please note that the height of this column selection should be exactly the same height as the array for the INDEX formula
Step 4: For the final argument in the MATCH formula, input 0 to perform an exact match and close out the MATCH formula
Step 5: Now that we’ve arrived at the column number entry of the INDEX formula, input another MATCH formula but this time select your horizontal lookup value for the lookup value input
Step 6: For this lookup array, select the entire top header row of the original grid you selected for the INDEX formula
Step 7: Repeating what we did for the previous MATCH formula, input “0” for an exact match and close both the MATCH formula and the INDEX formula with parentheses
What Excel Does
Excel must first calculate the result of the two MATCH formulas embedded within the INDEX formula. Since we know that “WA” is the sixth value down in the left hand column, and “2004” is the fourth value across in the top header row, those formulas become the values of 6 and 4 respectively. Once we’ve simplified those components, Excel essentially performs the exact same INDEX lookup that we demonstrated before; it goes down 6 rows and over 4 columns to pull the correct value of “$261.04”.
Summary
INDEX MATCH MATCH probably won’t be a formula you use often. Most of the time when dealing with databases and data tables, you’ll be using vertical lookups to query results. However, in situations where you absolutely do need to perform a matrix lookup, INDEX MATCH MATCH is the best option you have.
 13908 views
 2 answers
 0 votes

Hi Joey. The above stated formula is working properly. Kindly check the same at your end. if there is no luck, you can mail the file to EXCELROCKER@YMAIL.COM . I shall check and revert
 4218 views
 5 answers
 0 votes

Dear Nitesh,
None of the options are working.
 11753 views
 3 answers
 0 votes

Hi Joey,
Please type the formula in Cell C1″ =INDEX(A11:D14,MATCH(A1,A11:A14,0),MATCH(B1,A11:D11,0))” without quotes.
This answer accepted by Joey. on 3rd September 2015 Earned 25 points.
 4218 views
 5 answers
 0 votes

Hi mohammed,
Please find the steps taken from the website “http://www.excelguru.ca/content.php?279VLOOKUPforPictures”
VLOOKUP for Pictures
 13034 views
 2 answers
 5 votes

Hi Nitesh,
The code may Work, But question is to recover the password protected FILE / Workbook not Worksheet. It would be of great help if you can guide him some better way.
 3182 views
 5 answers
 0 votes

Hi,
No Doubt ALT+= is the keyboard shortcut to apply immediate sum function in excel provided the data is in continuous form.
 1767 views
 2 answers
 0 votes

ALT+I, W : 2003 Menu Access key
 2125 views
 3 answers
 1 votes

An Excel Ribbon is a tool Which Holds all the options or features of excel or spreadsheet application
 1995 views
 2 answers
 1 votes
Step 1: Creating the Picture Table
The first thing we need to do is create a table of our pictures. For this example, we’ll start with each of the potential weather conditions that could be returned by a weather feed, as follows:
This list is contained in A1:A11 on the “Pictures” worksheet.
Next, we need images for the forecasts. The example file (available at the link at the bottom of the page) contains weather forecast images snapped with my favourite screen capture program (SnagIt by TechSmith) which were then pasted in the table next to the correct description:
Now, there are a couple of really important things to notice here:
Next, we need to name the cells that hold the hold the pictures. Because we have a lot of them, the easiest and fastest way to do this is by using the “Create from Selection” feature:
What this will do for us is create a defined name of “rain” for B2, “isoshower” for B3, and so on down the table. You can verify that this works by selecting cell B6 and noticing that it says “rain” in the name box:
Great, now our table is set up correctly.
Step 2: Setting Up the Driver Cell(s)
So the next step is to build the plumbing that will drive which picture will be shown. This could be done in any number of manners including any of the following:
The possibilities are truly endless, with the only key being, the value in the driver cell MUST match one of the names in the Picture Name list that we have in cells A2:A11. To that end, we’re going to set up a quick table that looks like this:
Cells A3:E3 are text values that were entered, but A4:E4 were set up using a data validation list in this case. To do that:
You’ll now be able to select items from the list by clicking the dropdown arrows in the cells. Remember though, this is just for simplicity here, and you could drive these cells in via any manner or formula you wanted, so long as it returns an item that matches your list.
Now, we also need a few more names as well. In fact, we need a name for each of the individual days. So let’s set those up by doing the following:
Cool, so now A4 has a name of “Day_01”, B4 is “Day_02”, and so on. We’re almost there, but we need to make a minor modification to these named ranges. So let’s open the Name Manager:
What we need to do here is change the Refers To formula from =Forecast!$A$4 to =INDIRECT(Forecast!$A$4)
The reason for this is that cell A4 contains the text value of “rain”. What the INDIRECT function does is essentially tries to interpret the text as a formula. As it happens, we have a defined name called “rain”, which refers to cell B2 on the Pictures worksheet. So the INDIRECT function will return a reference to that cell for us!
Once you’ve updated that formula, make sure you also update the other Day_0x names as well:
Now, I know that this doesn’t look like much, but you’ve laid all the groundwork to make some magic happen!
Step 3: The Picture Lookup
What we need to do now is go and copy any picture from the Pictures worksheet. It doesn’t matter which one, but you need to do this correctly:
Careful here! You need to get the right icon to paste with. In Excel 2010 you’ll find it here:
Now, with that done, you have one more thing to do:
And that’s it! Try changing the value in A1 and see what happens… instant picture lookup!
To do the others, you just follow a similar route. Copy any cell, and paste as a linked picture. In fact, you can even paste the four remaining ones one after the other. Once done you need to select each picture and update the formula to =Day_02, =Day_03, etc…
Ending Thoughts
This is a really cool trick, doesn’t need a single line of VBA, and actually doesn’t even use a VLOOKUP function at all! It’s purely driven by the INDIRECT function pointing back to a cell range with the Camera object (invoked by the Paste Picture as Link ability.)
Do be warned that the camera object does have some issues, and you may not find the pictures stable if you use too many. In my experience, that’s been more than 10 linked pictures. (Although the table of pictures can have hundreds of pictures in it to choose from with no issues.)