Using a combination of the INDEX MATCH functions has many uses, we’ll look at each function in isolation to understand how they work, and then we’ll use them together as an alternative to the VLOOKUP formula.

### 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(array, row_num,[column_num])

In English:

=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)

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:

=INDEX(B17:F24,2,3)

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:

=MATCH(lookup_value, lookup_array, [match_type])

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 <= to the lookup_value. The values in the lookup_array must be in ascending order when using 1 or omitting this argument..
- 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.
- -1 finds the smallest value that is >= to the lookup_value. The values in the lookup_array must be in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

Ok, that's enough of the syntax.

In English and using the previous example:

=MATCH(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:

=MATCH(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.

=INDEX( 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:

=INDEX( 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:

=INDEX($B$18:$F$24,MATCH("Bat Man",$B$18:$B$24,0), MATCH(“East”,$B$17:$F$17,0))

So why would you put yourself through all that rigmarole when VLOOKUP can do the same job.

### Reasons why you’d 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:

=INDEX($B$33:$D$40,MATCH("Krafty Kids",$C$33:$C$40,0), MATCH("Program",$B$33:$D$33,0))

And you get the answer; ‘Mr Maker’.

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.

=INDEX(A4:J10,MATCH(A2,A4:A10,0),MATCH(B1,A4:J4,0))

*Note: An alternative is to use a VLOOKUP and replace the hard keyed column number with a MATCH formula like this:*

=VLOOKUP(A2,$A$4:$J$10,MATCH(B1,A4:J4,0),FALSE)

### 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.

Dinesh K Takyar says

Very well explained. Thank you!

Carms says

Can you use IF/INDEX/MATCH to find value in full sheet if information shifts and return 0 is info is not found? values shift around too much depending on available info to use vlookups/hlookups

Mynda Treacy says

Hi Carms,

You don’t need IF. That’s one of the benefits of using MATCH, it will locate the correct location and is not fixed to a specific column/row.

Mynda

Carms says

How do I do that and get 0 if value does not exist? This is what I’ve tried so far but it isn’t working

=INDEX(pos!$A$4:$U$72,MATCH($B$57&B59,0))

=IFERROR(INDEX(pos!A1:AT81,MATCH(Wkly!B60,pos!A50:V78,0),MATCH(Wkly!B57,pos!A50:V78,0)),0)

=INDEX(pos!A5:AT81,MATCH(B84,pos!A5:AT81,0),MATCH(B57,pos!A5:AT81,0))

Wkly is the tab I want my results listed on

Pos is the tab I am extracting info from

I am trying to get value at the intersection of B57 and B59 (for example)- but info in pos tab is not stable- shifts around (cannot locate a column or line or cells distance between name and amount to use vlookups/hlookups).

Catalin Bombea says

Hi,

Can you please upload a sample file on our forum? It will be easier to understand your situation. Make sure you prepare a clear manual example of the desired result.

Cheers,

Catalin

charlie says

help… I need to have a if/lookup/or match index for a sheet im working on…

person a 123 (formula needed ) 123= 10.00 456=20.00

person b 456 (formula needed )

I need something that will look at the person and then the number and from that pull the correct figure, a vlookup will only work on one set of numbers and will not change if i change the 123 etc…….

I have been trying for hours and help or guidance would be appreciated.

Catalin Bombea says

Hi Charlie,

You can upload a sample file with your problem on our Forum, it will be easier to help you.

Thanks for understanding.

Catalin

Bill Beshlian says

Mine is more of a question than a comment. Can you use Index and Match to compare values in different workbooks then return the result you want from the other workbook?

Mynda Treacy says

Hi Bill,

Yes, you can reference external workbooks with INDEX & MATCH. Just be sure to have both workbooks open to refresh the formulas.

Mynda

Stephan says

Index Match calcs! Didn’t take long to work this 1 out!

If F2 = rows 2 to 8, then, C – D = duration

=INDEX(B2:B8-C2:C8,MATCH($F$2,$A$2:$A$8,1))

But I’ve no idea on how to edit to include further lookup hence instead of more then 1 column?

Currrently just: A2:A8

ie now just MONDAY

but how to search rows for 4 column row match?

such as: MONDAY 21ST APRIL 2016

How to include a further 3 columns, to $A$2:$A$8 ?

such as instead of just Column A = Day

also to add

Column G: Date DD

Column H: Mth

Column I: Year

Catalin Bombea says

You can try joining 4 columns in a match:

MATCH($F$2&$G$2&$H$2&$I$2,$A$2:$A$8&$B$2:$B$8&$C$2:$C$8&$D$2:$D$8,1)

This will change the formula to an array formula, confirmed with Ctrl+Shift+Enter, not just Enter after editing.

Or, you can try a SUMPRODUCT, if you’re sure that it will be only 1 match for a search:

=SUMPRODUCT(($A$2:$A$8=$F$2)*($B$2:$B$8=$G$2)*($C$2:$C$8=$H$2)*ROW($A$2:$A$8))

This formula will return the row number where the match is found, you can use it in row_num argument of INDEX function.

Catalin

Stephan, England, Hull says

Hello Catalin I’ve uploaded a file to your help desk assigned ticket #480492: IndexMatchCountIf.xls

Quite simply I wanted to sum up some Hours! Currently I put the Hours info of Gannt Schedules into Tables to total, but thought surely there’s a concise formula that can do this!

My best formula method, IndexMatchCountIf.xls,1st tab, is my newly constructed Calcs with IndexMatch array formula that works with CountIf, hence cell refs: R1 & Q:Q, in this example for Row selected, ColF-ColE+ColG

=IF(AND($A$2=0),””,IF(ROWS($Z$2:$Z2)>$R1,””,INDEX(F:F,MATCH(ROWS($Z$2:$Z2),$Q:$Q,0))))-INDEX(E:E,MATCH(ROWS($Z$2:$Z2),$Q:$Q,0))+INDEX(G:G,MATCH(ROWS($Z$2:$Z2),$Q:$Q,0))

Index is a more concise formula, but only 1 column for criteria selection, cell ref $1$3:

=INDEX(F2:F20-E2:E20+G2:G20,$I$3)

Index Match without CountIf, works with 4 Column criteria, but this is currently inaccurate, possibly it is only correct with data in Numeric & AZ order:

=INDEX($D$2:$D$20,MATCH($I$30,$A$2:$A$20,0),MATCH(J30,$B$2:$B$20,0),MATCH(K30,$C$2:$C$20,0))

The purpose of this data sort is to display Hours totals into data tables, that can be used to plot charts, hence are a useful Management comparison, as data tables of dates & numbers to Porsche driving CEOs, MDs and ENTREPRENEURS, are just a blur of meaningless figures! Unless they’re already familiar with the dataset, hence why Managers like Bar Charts more then figures, they can see real physical differences in TIME or/and RESOURCES!

Stephan says

Can Index Match, or another formula combo, perform a single cell calculation using Cells that meet it’s Criteria?

To be specific, is it possible for Index Match or similar, to do within 1 CELL, a Formula match of DV Combo Box > Column A rows, then for that specific row of specific Columns in this instance Column B&C, perform a calc?

Such as: If Column A = *, then C2-B2=#

For example:

Column A= Day

Column B = Start time

Column C = Finish time

Row 1 = Header

Rows 2-13 = Data

Column A Column B ColumnC

Row 1: DAY START FINISH

Row 2: MONDAY 10AM 11AM

Hence if your:

Data Validation Combo in Cell F1 was MONDAY

Column title START TIME in Cell E2

Column title MONDAY in CELL F1

=INDEX($A$2:$C$13,MATCH($F$1,$A$2:$A$13,0),MATCH(E2,$A$1:$C$1,0))

Currently I’m performing Total calcs by copy data into a numeric grid just to do totals.

I realise you can achieve the same with a simple IF calculation,

But there are many rows of data, and with IF that would involve length edit to consider every individual type!

Hence my question for a formula, using just Column A row matches to make a calculation would be a great time saver.

And if there is such a Formula, what would be a Formula to Search, say 4 Columns, then perform :

example: ROW to search on table headings in “PREdefined” table with Column Headers and Row Headers:

lookup = MONDAY, 10TH, APRIL, 2016

then answer = FINISH TIME – START TIME = DURATION

Since Days of the Week is only 7 Days, then for Index Match to differentiate between all the different Mondays-Sundays, Columns:

DAY, DATE DD, MTH, and YEAR.

Catalin Bombea says

Hi Stephan,

Can you please upload a file with your data on our Help Desk? (You can open a new ticket.) It will be a lot easier to understand your situation and to provide a personalized answer. A manual example of a correct result will also be very helpful.

Catalin

Daryl Rae says

Hi,

Can vlookup or match – look at part of a cell then have the value next to it go into a cell instead of the whole cell.

ie; I down load bank account details via xls and it will have deposit by bill smith for address ref no 123 and then it will have an amount next to it say $100.00

I know if I enter =IF(F$41)=$K$1,$D41,””) then I enter something (manually) into F41 to Match K1 then it enters the amount next to it into the cell I write the (IF Formula)

can I just match or lookup part of the cell to then say the (address only) then have the amount enter the cell automatically?

Thankyou for your help with this, love your tips

Daryl

Catalin Bombea says

Hi Daryl,

You can use a wildcard for partial matches:

=MATCH(“*”&B2&”*”,A1:A10,0)

Using an asterisc before the search value will tell the function that the match can have any chars before the search string, same if you use the wildcard after the search value. Using the wildcard before and after search value, means that the search value can be anywhere in the cell: at the beginning, at the end, in the middle, and it will match full strings also.

Catalin

jjknipp says

Can Anyone explain to me why excel is preventing me from putting brackets around the index match function (ctrl,alt,enter)?

Mynda Treacy says

First it may be that you’re using the wrong key strokes, it’s CTRL+SHIFT+ENTER, not CTRL+ALT+ENTER. Also, (just in case), you don’t type the curly braces in before prssing CTRL+SHIFT+ENTER?

Let me know if that doesn’t help.

Mynda

Catalin Bombea says

Hi,

Those curly brackets seen in the formula bar are not manually typed, excel will automatically create them when you press Ctrl+Shift+Enter. If you are already doing this, and excel is not accepting the formula, then the formula might have another problem.

Catalin

AQ says

please can you describe to the following structure of MAtch in the below formula

=IFERROR(INDEX(Input,MATCH($H$30&”|”&ROW(A4),Input[UNIQUE VALUE (CALCULATED)],0),2),””)

Mynda Treacy says

Hi AQ,

It’s a bit tricky to give a thorough explanation without seeing the workbook but, basically it’s looking up the value in cell H30 and joining a pipe symbol and the number 4 (as returned by ROW(A4)), in the range returned by Input[UNIQUE VALUE (CALCULATED)], and matching it exactly.

I hope that helps.

Kind regards,

Mynda

Livingston says

Thank you very much for this excel tips and lesson. Since I subscribe to myonlinetraininghub, I learnt a lot of new skills in excel. I’ve told my friends about this and they are excited.

Again thank you very much for sharing.

Mynda Treacy says

Thanks, Livingston. Glad we can help you master Excel.

Mynda

Ally says

Hi!

I’m using INDEX MATCH on a spreadsheet that looks at two cells which contain validation lists, then matches the values that have been selected with the row and column headers in my table and returns the result.

This is what I am using:

=INDEX(X6:AI11,MATCH(B5,MONTHS,0),MATCH(Q5,TEAMS,0))

This works up if January-June are selected in the validation list, then returns #REF! For the rest of the months and I can’t work out why, any help would be much appreciated!

L.E: Sorry I should have said, MONTHS is the named range of X5:AI5, TEAMS is the named range of W6:W11

Thanks

Ally

Catalin Bombea says

Hi Ally,

Please upload a sample file in our Help Desk, it’s impossible to say what’s wrong just from your description.

Thanks for understanding

Catalin

Ally says

Thank you so much for your help, and the fast response from the help desk! 🙂

Catalin Bombea says

You’re wellcome Ally 🙂

Adam says

Hi,

I’m trying to get a return value from a pivot table.

Rows A4 – O4 are the date range and column A5 – A35 are the work orders. (B5 : O35 are the hours worked)

I’m using the command

=INDEX($A$4:$K$35,MATCH(S23,$A$4:$K$4,0),MATCH(U23,$A$4:$A$35,0))

and it’s not returing what I think it should be returning.

Please help.

Thanks

Catalin Bombea says

Hi Adam,

The second argument of INDEX function refers to row number. Your function for this argument is: MATCH(S23,$A$4:$K$4,0)

Looks like the range refers to the header row, you should provide a range of rows, not a single row.

Same problem with third argument: MATCH(U23,$A$4:$A$35,0) should return the column number, but the range refers to A4:A35. You should provide here a range of columns from a single row, exactly like the range from previous Match: $A$4:$K$4

Obviously, you should switch the places for these 2 arguments:

=INDEX($A$4:$K$35,MATCH(U23,$A$4:$A$35,0),MATCH(S23,$A$4:$K$4,0))

I don’t know what you have in U23 and S23, check if you have to switch these cells too…

Catalin

Joao says

Hi there,

I am not sure how to explain what I want help with so Im just gonna give an example:

A B C

1 1

2 2

3 3

4 3

5 3

6 2

7 3

8 3

9 3

What I need is in column C, for a cell that has 3 on column A, to get the value of column B, for the cell above that has value 2.

So for cell C9 I want the value of the column B6, because A6 has the number 2 which is the first above C9.

Do you think this is possible with Index Match formula?

Thanks in advance

Catalin Bombea says

Hi Joao,

Please use this file created on OneDrive to give us more details on this problem, you can even complete column C manually , to show us the logic behind this. If you can share another example, where you will apply this solution, maybe we can come up with another solution to do it, my guess is that you are not on the right track, if you need such twisted solutions 🙂

You can edit the file in browser, no need to download or upload again, just let me know when you finished these details.

Cheers,

Catalin

Mike Kraft says

Q: I’ve got two tables that I’d like to “sum” index and match.. Sometimes one table does not have a value which then renders my sum as “#na”..

in that case, I’d like my formula to “choose” the table with a value and report that back. Make sense?

Mynda Treacy says

Hi Mike,

Kind of. I’m wondering if SUMIF and IFERROR will do what you want. Can you please send me a workbook with an example via the help desk so I can see exactly what you’re dealing with.

Thanks,

Mynda.

Mike Kraft says

Thanks Mynda.

I submitted my help ticket today 🙂

khalid says

excelllent stuff !!

Philip Treacy says

Thanks, khalid 🙂

Georgi says

I have

A B C D

1 a 1 a formula

2 a 2

3 b 1

4 b 2

5 c 1

6 a 3

I am using =INDEX(B:B;VERGLEICH(ca;a:a;1)) It gives me the result 2

germen excel – vergleich=match

The question is how to make the formula to work for the next “a” and to give me the result “3” as it is?

Mynda Treacy says

Hi Georgi,

You can use this INDEX & MATCH Formula to find the last instance of your match:

This is an array formula so you must enter it with CTRL+SHIFT+ENTER.

You can read how this formula works here.

Note: since this is an array formula I do not recommend referencing whole columns like B:B, your computer might hang. It is better to either use a dynamic named range, or just a range that allows for some growth in your data.

Kind regards,

Mynda.

Georgi says

Hi Mynda,

It is not working. The result is 1. I need the MAX number ever entered for “a” which is 3… 🙁

Mynda Treacy says

Hi Georgi,

It works for me. Did you enter it with CTRL+SHIFT+ENTER? It is an array formula.

Kind regards,

Mynda.

manuchehr soleimani says

very very thank you

Mynda Treacy says

You’re very, very welcome, Manuchehr 🙂

Bob says

Nice and simple to understand compared to other index and match instructions.

Mynda Treacy says

Cheers, Bob 🙂

SIMBARASHE SILENDENI says

Hi Treacy

i have got the following formula to calculate monthly deprecation;

= IF(U18<J19,0,(D19*H19*(1-F19)/(E19*12)))

and this is calculating my monthly depreciation expense correctly starting on the correct month for the 5 yr period which I have on my table. However, the problem with my formula is that it is not telling excel when to stop depreciating the assets.That is, the formula above continue to compute depreciation even after the 5 yr period.How can I put a condition on this formula to tell excel to stop calculating depreciation after 5yrs?

Mynda Treacy says

Hi Simbara,

It’s a bit difficult to give you a specific solution since I don’t know what the cell references are pointint to, but you need to put a condition in so that your IF formula reads:

=IF(age < = 5years, then do calculation) If you get stuck it's best if you can send us the workbook via the help desk so we can see what you’re working with.

Kind regards,

Mynda.

SIMBARASHE SILENDENI says

Hi Treacy,

I repeat my formula below for your quick reference;

= IF(U18<J19,0,(D19*H19*(1-F19)/(E19*12))), where;

-U18 is my starting month for my depreciation table which in this case is Jan 2013)

-J19 is the asset acquisition date (which is the starting month to charge depreciation) and on the above formula that month is March 2013

– D19 is the acquisition cost per unit

-H19 is the number of units purchased

-F19 is the residual value of the asset (in this case 5%)

-E19 is asset's useful life in years.

My depreciation table runs horizontally for 5yrs starting Jan 2013. My problem is to define the cut off month for charging/calculating depreciation on my depreciation table.

I hope u will be able to help me now.

Thanks

Simba

Mynda Treacy says

Hi Simba,

The reason we ask for you to send your workbook to our help desk is to make it easier for us to answer your question and save us having to recreate your data to test a solution.

Anyhow, here is a formula that may work:

If it doesn’t work please send me your workbook via the help desk (take out the sensitive information if relevant), so that I can see your data and give you a tailored answer.

Kind regards,

Mynda.

SIMBARASHE SILENDENI says

Hi Mynda,

Your formula has worked perfectly. A very glad and thanks very much for coming to my rescue.

Regards

Simba

Mynda Treacy says

You’re welcome, Simba 🙂

SIMBARASHE SILENDENI says

Hi Treacy,

I have another problem.I have a formula like this to compute monthly loan payments;

=IF(E5*F2>D5,0,E5) where

E5 is the scheduled monthly loan instalment amount

F2 is the month number(in this case 1st month of payment, running horizontally from 1 to 60)

D5 is the total loan amount

My formula above is not able to tell excel to put the last instalment figure if it is less than the scheduled amount in E5, ie if the scheduled amount is $10,000, but the last instalment is say $8000, the above formula puts zero instead of $8,000.

Please help

Simba

Carlo Estopia says

Hi Simba,

The best thing to do with this is for you to send your mock data with some explanations

as to how you want your formula to be : HELP DESK.

Cheers,

CarloE

SIMBARASHE SILENDENI says

Hi Treacy,

I have a formula which is giving me this error #VALUE! even if I try to trap the error using IFERROR. The formula is linking correctly all the cells with values, but gives the above error where the linked cells are blank. How else can I trap the error to give the zero value instead of #VALUE! error.

Regards

Simba

Carlo Estopia says

Hi Simba,

Is this related to your previous message?

If so, we would like to request your file

and send it here: HELP DESK.

Cheers,

CarloE

Carlo Estopia says

Hi Simbarashe,

You may try a formula like this one:

Just substitute your formula in the true value in the IF formula like this:

Shane says

I am having some trouble trying to figure out a formula that I think INDEX/MATCH could be used for. I have a cell that I am trying to get the SUM of two multiplied numbers. The problem is that I am trying to insert a max number by looking up two criteris in the work sheet.

I will try to give an example.

I am multiplying E19,E20. Lets say this gives me 15. I want the max this number could be to be 12.31.

I have a table on another sheet that has four different collumns: Number of Years(0-50), a multiplication rate for each year, the max number for each year, and a status code(standard, super, and Exec).

I want to look at E6 to find the number of years and then look at B5 to find the status code and figure out if the two numbers multiplied come up with a number higher than the max(in which case it would insert the max number) or a number lower than the max(in which case it would just insert the sum of the multiplication).

Sorry if this is confussing, but it is the best way I can think to describe my problem.

Mynda Treacy says

Hi Shane,

Thanks for your question. If you can send us your Excel file via the help desk (or an edited version if it contains sensitive information) it’s probably best. That way we can give you a customised solution.

Cheers,

Mynda.

Harshu & Shreyu says

I Like the way you teach, it is very simple and easy to understand.

your examples are very much in detail and informative. Many thanks

for your effort & time.

Mynda Treacy says

Thanks Harshu & Shreyu 🙂

PAVLOS LEOUSIS says

Thanks again, you are trully a great help

Mynda Treacy says

You’re welcome, Pavlos 🙂

shazif umair says

Hi Mynda,

I am having a problem and I need your help to get out of it.

In both Look up and Index Match formula we have to give two references one from the column and other from the row but can there be any possibility where references from two columns and one row could be given.

Let me elaborate it by an example: I have CODE of employees in Column A, Dates in Column B, Quantity or Production of 5 different Operations in Column C to G. ( a person works on more than one operation in a particular date)

Above is the data and i need the formula so that I give the code, operation and date and the formula gives me the quantity by looking up all three of the variables given.

I have tried by putting “And” function with Index Match but its not working.

Waiting to get a positive reply from you anxiously.

Regards,

Shazif

Mynda Treacy says

Hi Shazif,

I’d use the SUMIFS Function for this.

Kind regards,

Mynda.

Teuta says

HI!,

I’m having trouble with INDEX MATCH formula.

The Basics data are:

1.Departure time of Airline

2.MOnth

3. A percentage incentive based on the month and Departing time.

What I undestand is that I have a problem with Time Format. I Try to use each MATCH formula for MOnth and Time to see where’s the problem , and MATCH TIME formula doesn’t work ( N/A).

The first Sheet has the real info ( the real time of departing in each day the month), and the second one has the entire info of percentages per earch departing possible time and per each month. If is needed i may send the excel but is a simple case of INDEX MATCH formula.

Thank you in advance.

Mynda Treacy says

Hi Teuta,

You’d need to send me the file via the help desk as I can’t picture what the problem might be.

Kind regards,

Mynda.

Bob says

I have a financial spreadsheet that tracks a portfolio. Column A1:A264 contains the weekday dates and column N7:N264 has the running total whether up or down since I started tracking. There are currently 2 worksheets, 2013 and 2012. The summery section tracks the current balance and I use the formula =MAX(‘2013:2012’!N7:N264) to display the highest value my portfolio has been and then the difference between the two. I am trying to come up with a formula to display the date that the high was reached and am currently using =INDEX($A$7:$A$264,MATCH(MAX(‘2013:2012’!$N$7:$N$264),$N$7:$N$264,0)) but that is only looking at the 2013 worksheet. What am I missing? Thanks!

Mynda Treacy says

Hi Bob,

When looking multiple sheets you need something a bit different. Here is a tutorial on looking up multiple worksheets.

I hope that helps.

Kind regards,

Mynda.

ABBAS says

HI THANKS FOR THE DETAILS

IF THERE ARE ROWS (100) WITH MULTIPLE BATMAN AND I WANT TO SHOW ALL OF THEM WITH ASSOCIATED VALUES HOW DO I DO THAT.

THANKS.

Carlo Estopia says

Hi ABBAS,

I’m afraid this can’t be done by Excel alone.

I may be wrong, Excel lookups aren’t built for this type of situation.

I don’t even think Array Formulas can do this.

Cheers,

CarloE

RENUKA DEVI says

Hi,

Your online training hub give us good explanation. Thank you for that first of all. I have doubt you have showed me two way look up. but IF i need to refer

two are more column then what should i do?

I am fresher. I need your help in referring three column such as Brand, Model, Segment and record the sales volume. which was done manually before my arrival to the company

Carlo Estopia says

Hi Renuka,

Please do send your file via HELP DESK and

please explain this further. I am

suggesting you should use some illustrations.

Cheers,

CarloE

Raghu says

The table range is B17:F25, where as you have take it as “$B$18:$F$25”. You have actually omitted the headings. Why? Could you clarify this point?

Carlo Estopia says

Hi Raghu,

Let’s begin with the very basic INDEX example in the post.

Now let’s remove the heading which is in row 17.

Notice that it’s still 91 but this time, the first row is now in 18; hence,

we changed 2 to 1 while col 3 remains.

The point is that INDEX or even VLOOKUP don’t really need headings to work.

All it needs is that it will lookup all that is within its Table_Array.

So to clarify your question, Had Mynda used B17:F25, then the rows part would be

adjusted because the MATCH Function will start also at B17. In other words, Batman

will get a match in row 2 because the first row is 1 and the second row is 2.

Hence, INDEX(Array, ‘match at row 2’,col 3).

If it’s B18:F25, Then Batman will get a match at row 1.

Lastly, try to isolate the match functions.

Try to change the starting row

from 18 to 17. Note: This is matching B10:Bob the Builder which is found

in the table at row20 or row number 4 basing on our table if including

our heading(starting at row 17).

In other words, without the heading(starting at row 18), it’s in row 3 of the array.

Cheers.

CarloE

Mike D. says

I am trying to use data that has been input into two cells on the same row (cell G3 and cell I3) and look these up in a table to pull the value that the table provides for the combination of these two. G3 represents which quartile of a salary range an employee’s salary sits (so the choices are Q1,Q2,Q3,Q4). I3 represents the employee’s performance rating (there are five 2-letter rating possibilities). The table shows what % salary increase is recommended for the combination of the two values. For example, if Q2 and EC rating it should pull 4% from the table.

This is the formula I have used:

=INDEX($W$5:$AA$9,MATCH(G3,$X$4:$AA$4,0),MATCH(I3,$W$5:$W$9,0))

It does not seem to pull from the correct cell in the table.

Any suggestions on what I have done wrong?

thanks!

Mynda Treacy says

Hi Mike D,

You have a couple of problems:

1. the range you are indexing shouldn’t include column W.

2. You’ve got your row_num and col_num arguments back to front.

Your formula should be:

I hope that helps.

Kind regards,

Mynda.

Dylan Butler says

Hi, I am trying to figure out a formula I can use for a question I have been given and I am nearly sure I need to use match and index , the question is:

’14 machines have been bought over time and are of four different types. Column L(letters a-n) and M(numbers 1-4) give the machine type corresponding to each machine. Put a formula in column F so that the machine type is given for each breakdown’

the corresponding letter for the machine is in column e

I can send a copy of the workbook if you require it ! , any help given would be greatly acknowledged,

Regards

MikeBanawa says

Hi Dylan! What I would suggest is to identify in

Column Mthe specific type of machineColumn Lbelongs to. You’ll have to do this per Row. This way, you’ll end up with an array that does not need Index and Match anymore. Here is a sample data withColumn L as the machine nameandColumn M as it’s type:LM

a1

b2

c3

d4

e1

f2

g3

h4

i1

j2

k3

l4

m1

n2

Instead of Index-Match, this data can now be used as a table array for a much simpler formula which is

VLOOKUP. A sample formula would be:=VLOOKUP(E1,$L$1:$M$14,2,0)

Please let me know if this is the answer you are looking for. If not, We’d love to hear from you by shooting us an email via the help desk. You can attach a copy of the workbook there and add more details on what you want to see.

Thanks!

Mike

Craig Fraser says

I am trying to lookuo two criteria (on one row) on worksheet 1 and match it to two criteria (in one row) on worksheet 2 and return a coresponding value from a cretain colum in the row. At present my formula is as follows

IF(ISNA(INDEX(Sheet3!$H:$H,MATCH($A111&$I472,Sheet3!$A:$A&Sheet3!$F:$F,0))),””,INDEX(Sheet3!$H:$H,MATCH($A111&$I472,Sheet3!$A:$A&Sheet3!$F:$F,0)))

Problem is a) it’s not returning the value in the coresponding cell.

b) it takes an hour to process the sheet, i need to shorten the processing time.

MikeBanawa says

Hello Craig!

Thanks for bringing this question up. First of all, what we’re trying to get is data from a

certain tablein Sheet 3. However, the formula provided above is a bit incompatible with each other:As you can see:

IF(ISNA(INDEX(Sheet3!$H:$H,– the Array does not match the lookup_array in your MATCH formula:MATCH($A111&$I472,Sheet3!$A:$A&Sheet3!$F:$F,0)– so instead ofSheet3!$H:$Hthe Array should be something like this:

Sheet3!$A:$Hsince you’re trying to get a value from Sheet 3’s Columns H, A and F.It is also important to know that although Microsoft Excel is a very powerful computing tool, in this case, it does not understand your use of “&” because the formula is not made to read that kind of criteria.

Now, assuming

A111is looking to match a criteria inSheet3!$A:$A, andI472needs to match something inSheet3!$F:$F, even if we used a correct formula, you still won’t be able to return the data incolumn H:H. It will just show whatever match I472 found in Sheet3!$F:$F.One solution for this is to insert additional columns per sheet and use the VLOOKUP formula:

– The additional column in Sheet1 will contain this formula: $A111&$I472 and let’s say the column will be inserted in Column A.

– The additional column in Sheet3 will contain this formula: $A1&$F1 and let’s say the column will be inserted in Column A.

– Now we’ll use them in the VLOOKUP formula assuming that your data is until row 1000:

=VLOOKUP(A1,SHEET3!$A1:$H1000,8,0)

Breaking that formula up:

1. A1 is the Lookup value and the result of the formula $A111&$I472

2. SHEET3!$A1:$H1000 is the whole table in Sheet 3 where we’ll get the results and,

3. Finally, F8 is the column number of H from A through H.

Another solution is to use this VLOOKUP formula that looks up multiple criteria, although it only solves part of the problem, you still need one helper column for the lookup table.

To address your performance issues; the main reason for the calculation slowness is because the formula is referencing whole columns like $A:$A etc. This means you are commanding Excel to check all rows in Column A.

Also, since you combined IF and ISNA to handle errors, your INDEX/MATCH formula is actually repeated twice per row. If you have Excel 2007 or above you can use the IFERROR function instead of ISNA which is much more efficient.

If you’re still stuck please send us your excel file via helpdesk, explaining exactly what you want and where. That way I’ll be able to help you find the best solution to your Excel problem.

Thanks!

Mike

Lee says

=IF(ISNA(INDEX(B$7:BV$42,MATCH(A94,A$7:A$42,0),MATCH(C$88,B$4:BV$4,0))),””,INDEX(B$7:BV$42,MATCH(A94,A$7:A$42,0),MATCH(C$88,B$4:BV$4,0)))

I created this function to deliver a value from an index or leave the cell blank if the index found no data, which would deliver a NA error. This function works well.

The problem is now I need to multiply the information that the above function finds and multiply it by another variable. For example,

=(IF(ISNA(INDEX(B$7:BV$42,MATCH(A94,A$7:A$42,0),MATCH(C$88,B$4:BV$4,0))),””,INDEX(B$7:BV$42,MATCH(A94,A$7:A$42,0),MATCH(C$88,B$4:BV$4,0))))*$B$1 ; however, when I do this I get a “VALUE” error in cells that the original function generated a blank.

=(IF(ISNA(INDEX(B$7:BV$42,MATCH(A94,A$7:A$42,0),MATCH(C$88,B$4:BV$4,0))),””,INDEX(B$7:BV$42,MATCH(A94,A$7:A$42,0),MATCH(C$88,B$4:BV$4,0))))*$B$1 works fine if the original function found a variable.

Do I need to nest the original function into another IF function to return a blank instead of the “Value” error; I hope not, it would be a novel.

Any help would be appreciated. I am a novice at this stuff, it took me two days to figure out the original function. Your explanation will need to be dumbed down for me to get thanks.

Lee

Mynda Treacy says

Hi Lee,

If you’re using Excel 2007 or higher you can use IFERROR in your formula to handle the errors like this:

If you’re using Excel 2003:

Kind regards,

Mynda.

lee says

hi Mynda,

I run this football predictions game down me local and have everything stored on me comp, but it takes me ages marking them, i have everything else set up tables with formulas, i was just wondering if you could help me with one that would make marking easier, as in say

A1 A2 A3

result pts prediction

1-2 3 1-2

1-2 1 1-3

1-2 1 0-2

1-2 0 1-0

1-2 0 0-0

1-2 0 1-1

So far I got =MATCH(A3,C3,0)*3 just to give me the spot on score in A2

your help would be appreciated

yours lee

Mynda Treacy says

Hi Lee,

Sorry, I’m not following you. It might be easier if you send me the Excel file so I can see how your data is laid out and exactly what result you want where.

You can send it to me by logging a ticket on the help desk.

Kind regards,

Mynda.

nestiyante says

I need to find data from 3 different tables. What function should I apply? Could I use index and match function, or it would be better to use vlookup function, or any other function could be applied or used together?

Mynda Treacy says

Hi Nestiyante,

I’m not sure if your data is on different sheets, or all on the same sheet. Here is a tutorial on looking up multiple sheets with VLOOKUP.

I hope that helps.

Kind regards,

Mynda.

Swapnil Bhavsar says

I have 2 column in excel 2007. In A column, contain 10,000 different mail Ids and in B column contain few Id. I want to remove all mail Ids of column B from A and want remaining in C. Automatically B1 search from A1:A10000 and if it found same email Id (B1) in A column then it should be removed and remaining 9999 Ids should be in C column. Can you please help me ?

Mynda Treacy says

Hi Swapnil,

You can use Conditional Formatting to highlight (fill cell with a color) any id’s in column A that are in column B. First select all cells in Column A you want to check then insert this formula as your formatting rule:

Note: if your id’s in column B are in more cells than B2:B9 change above formula accordingly.Then use the Filter by Fill to hide the highlighted cells in column A.

Then copy column A and paste it in a new worksheet. When you copy and paste a filtered list it doesn’t copy the cells that are hidden by the filter.

Kind regards,

Mynda.

Swapnil bhavsar says

Thanks you very very much Mynda mam..First i was confuse when i refer attached sample file sent by u because i was expected formula in C. Any way, you gave me short cut way..which help me lots..Thanks once again..

sam says

too good….

Mynda Treacy says

Cheers, Sam 🙂

Frank-Oh says

Here’s a question that may or may not have something to do with the INDEX/MATCH functionality.

When in a cell containing that formula, in the formula bar it seems to be surrounded by { and }. If I enter the cell to modify the formula those 2 bracket characters, { and } , disappear. When I make the modification (to the index range, or any other piece of the formula), the formula fails and returns a “#VALUE!”. All remaining cells that still have the ghost { and }, still perform properly.

Any insite for me?

Thanks,

Frank

Mynda Treacy says

Hi Frank-Oh,

Those curly brackets indicate that the formula is an array formula. When you enter array formulas you use CTRL+SHIFT+ENTER, not just ENTER as you do for regular formulas. When you press CTRL+SHIFT+ENTER together Excel automatically inserts the curly brackets for you.

Array formulas work differently to regular formulas. You can read more about array formulas here.

Kind regards,

Mynda.

Chris says

At long last I have found someone who can actually explain how a function works in simple terms that everyone can understand. Congratulations and keep up the good work

Mynda Treacy says

Thank you, Chris 🙂

Cam says

I’m a seasoned VLOOKUPer. I’d like a one-liner comparing VLOOKUP to INDEX-MATCH. I don’t need to know the how or why of INDEX-MATCH. I just need to know “In VLOOKUP you put this so in INDEX-MATCH you put this” comparing them using the same terminology.

If you can do that, you’d save me a lot of brain cramping.

Cam says

Never mind. I found it: http://www.mrexcel.com/forum/excel-questions/79967-vlookup-vs-index-match.html#post388806

Ghopalen says

Hi,

I would like to add three adjacent cells at every five intervals in a single row. Please help

Mynda Treacy says

Hi Ghopalen,

I’m not 100% clear on what you mean but you could simply enter the first two formulas and then copy them across the row as far as you need.

Kind regards,

Mynda.

Mikan says

Hi teacher Mynda, please just erase my first comment, as i already have found out the answer. btw, thank you so much for these whole tutorials.

Mynda Treacy says

Hi Mikan,

Glad you figured it out 🙂

Mynda.

Mikan says

Hi teacher Mynda, I’ve tried the formula but i’m getting #N/A result. Why is it so?

Thanks a lot.

Ismail Safwan says

Hi,

I need to know why we are using $ symbol in formula. What is the defination for $(Dollar) Symbil

Thanks & Regards

Safwan

Anil says

Excellent clarity in your explanation. I am scratching my head on what formula to use so that Excel pulls out data from the first row whenever a match is found within the range. INDEX and MATCH allow give the result at the intersection but I am looking for column match but select data from the first row from the corresponding to the column matched. Would greatly appreciate your help. Thanks.

Mynda Treacy says

Hi Anil,

You can use this formula to find the MAX value in a row and return the corresponding value in row 2 of your workbook. The combination of absolute and relative references in the ROW function of the formula allow you to copy it down the column and it will dynamically update.

Kind regards,

Mynda.

Anil says

Hi Mynda:

That is awesome! Thank you very much for your prompt response.

Regards,

Anil

Khurram Ali says

Dear Mynda,

Thanks for elucidations.

In order to understand more and practice, I downloaded the attached workbook but it has bugs……..I am sorry! Its not legible or in excel format.

Please advise.

Khurram Ali

Philip Treacy says

Hi Khurram Ali,

When you save the file make sure the file extension is .xlsx

Internet Explorer sometimes changes the file extension to .zip rather than .xlsx

Also .xlsx workbooks are for Excel 2007 or newer

Regards

Phil

Minku Bhatia says

Hi Mynda,

Beautifully explained, the time and effort you are putting is great.

Thanks for helping us. how to attach file we we have questions.

Thanks

Minku

Mynda Treacy says

🙂 thanks, Minku. You can send me files via the help desk.

John says

Please Help,

I understand how if I have a “code” to input in column A and with vlookup find the “title”, but what if I have the “title” (column B) and need to find the “code” (Column A)?

Thanks John

Mynda Treacy says

Hi John,

You can either use INDEX and MATCH as I have done above in the ‘Reasons why you’d use INDEX and MATCH rather than VLOOKUP’ section, or you can use VLOOKUP with the CHOOSE function to trick VLOOKUP into looking to the left.

I hope that helps.

Kind regards,

Mynda.

John says

Thank you Mynda for your help. I’ll read up on the Choose function and see where it leads. Thank you so much for your time.

Best regards,

John

George says

I see the light 🙂 great explanation and thanks for the workbook download

Philip Treacy says

No worries George, you’re welcome

sanjay says

It’s a very good experenice to online learn excel formulas. It really helped me

Mynda Treacy says

Thanks, Sanjay 🙂

shambhu says

very good explained.

Mynda Treacy says

Thanks, Shambhu 🙂

Mahesh Murthi says

Very well explained

Mynda Treacy says

Cheers, Mahesh.

Scott says

Quick point of clarification (and THANK YOU for one of the MOST INFORMATIVE/MOST EFFICIENT explanations of a potentially complicated formula combinations)…

In the Batman/East example above, the description of the INDEX function states “=INDEX( return the value in the table range B17:F25 in the cell that is at the intersection of, MATCH( the row Bat Man is on) and, MATCH(the column East is in)”, yet all of the formulas given show B18 (EIGHTEEN) as the INDEX search range…

Does the title row range need to be included in the first INDEX range?

Thank you for further clarification…

Mynda Treacy says

Hi Scott,

Wow, thanks. I’m glad to have helped 🙂

Sorry about the slight confusion with the index range. It should be as per the screen shot i.e.

=INDEX($B$18:$F$24,MATCH(“Bat Man”,$B$18:$B$24,0), MATCH(“East”,$B$17:$F$17,0))

not:

=INDEX($B$18:$F$25,MATCH(“Bat Man”,$B$18:$B$25,0), MATCH(“East”,$B$17:$F$17,0))

Note: The formula still works when row 25 is included in the range.Thanks for pointing that out. I’ve since corrected the post.

Kind regards,

Mynda.

pavan says

ok

Jason P Woods says

Thanks

Mynda Treacy says

Hi Jason,

Glad you liked it 🙂

Mynda.

Charlie says

I am trying to combine index/match function in the following application:

1. In sheet1 I have column A with large sentences containing words like shoes, jeans, pullover, heels etc.

2. In sheet2 I created a table. In column A I entered the lookup words and in column B the return words.

3. My objective is to lookup words in sheet1 column A and when finding these words, return in Sheet1 column B the return words specified in sheet2 Column B.

4. I created the following formula, but it will not work.

=INDEX(Sheet2!B:B;MATCH(TRUE;ISNUMBER(SEARCH(Sheet2!A:A;B2));FALSE))

Do you have any idea on how to arrange the above in order to make it work as expected?

Mynda Treacy says

Hi Charlie,

Thanks for your question. I think the idea of using an INDEX, MATCH, and SEARCH functions won’t work for this application. One of the problems is you’re not telling Excel what to SEARCH for.

The SEARCH syntax is =SEARCH(find_text, within_text, start_num) but the ‘find_text’ criteria in your formula is the whole of column A. SEARCH(Sheet2!A:A;B2)

If you want to send me your workbook I’ll take a look.

Kind regards,

Mynda.

bill says

I have used INDEX MATCH many times and found this another excellent example of how to apply this formula.

Ramesh says

Me too , Love reading your articles. What makes this blog diffeernt is the level of details. Keep continuing the good work

Queenie Petrella says

I’m still learning from you, as I’m making my way to the top as well. I absolutely love reading everything that is written on your website.Keep the aarticles coming. I loved it