This tutorial demonstrates the key strengths of Excel's INDEX / MATCH function that make it superior to VLOOKUP. You will find a number of formula examples that will help you easily cope with many complex tasks when VLOOKUP fails. Continue reading
Comments on: INDEX & MATCH in Excel - better alternative to VLOOKUP
by Svetlana Cheusheva, updated on
Comments page 10. Total comments: 614
hie help me on this one, which would I use to rank students using index match. One student is being dropped if they are sharing the same position. for example
Table 1
STUDENT NAME MARKS POSITION
MIKE RICHARD 560 1
CARLO JONES 555 2
RUTH TONEY 555 2
BRIAN JAMES 552 3
I want the formula to give me results as shown in table 1 above but instead I am having results as indicated below in table 2 with 1 student missing on the list
Table 2
STUDENT NAME MARKS POSITION
MIKE RICHARD 560 1
CARLO JONES 555 2
RUTH TONEY 555 2 - (missing on the list)
BRIAN JAMES 552 3
which formula should I use to have all students on the list even those sharing the same pos
Hi Svetlana,
I am trying to create a list of items not shipped out from our facility.
I have a workbook with 6 pages. Each pages represents one size of cylinders we are shipping. Each cylinder has numbers in numerical order listed down columns on the page.
Example:
RJ001
RJ002
RJ003
etc.
Each row of the cylinders has information like customers name, date ordered, order number, etc.
One column in the row I enter the "date shipped". I am struggling to create a summary page that will list ALL cylinders from ALL pages that has no "date shipped" filled in. I am just looking to list the cylinder numbers on the summary page which has the "date Shipped" column not filled in. Then when I fill it in, it will be removed from the list. Am I looking at the right formula for this or am I aimlessly struggling for nothing? Any help would be appreciated. :)
Hi,
I have data in a worksheet like
Date Line absent %
8-Jan-17 A-1 7%
9-Jan-17 A-1 6%
10-Jan-17 A-1 3%
I want to get this data in another worksheet, where I will enter date and line name then the absent% will come automatically. Could you please help me ?
Hi Svetlana,
This is an excellent blog! Could you please help me with this question? Using the Sample Data below, what formula(s) could I use to lookup the Product Number and find all of the Product Names assigned to that Product Number? (Please see Expected Results below depicting how I'd like to summarize the results from the Sample Data.)
Sample Data
Product Number Product Name
111 Zoloft
111 sertraline
222 Advil
222 ibuprofen
222 Motrin
333 Advair
444 amoxicillin
444 Amoxil
555 APAP
555 Tylenol
555 acetaminophen
666 Vimovo
Expected Results of the Formula(s)
Product Number Product Name
111 Zoloft, sertraline
222 Advil, ibuprofen, Motrin
333 Advair
444 amoxicillin, Amoxil
555 APAP, Tylenol, acetaminophen
666 Vimovo
Thank You,
Reba
Thanks very much for posting this! It helped me potentially hours of work and lowered the chance of making manual errors.
Hi Svetlana,
I've spent the entire day on this blog searching for a way to solve the problem given inline and I must say WOW you are an amazing expert :), hope you will be able to help me too with the following problem and advise which formula to use for the following problem:
I have a list of cca. 18.000 customers mixed in cca 100 cities inside one country.
Country is divided by 8 regions where 1 region has cca 7-8 cities under it. I have a separate table/list which cities corresponds to which region.
I need to assign each of the 18.000 customers to a specific region, based on the city they are in.
Logic of the list is:
Column B2:B18001 contains cities names (multiple customers per one city)
Separate table has a list of all cities under one specific region (in total 100 cities divided in 8 regions - with luckily no overlaps or false entries)
I need the formula that will find all cities in column B that belong to these 8 regions and in the column C place a corresponding region name next to the city value (do 'cities per region' division).
So it's all textual data, with multiple 'recognise' and IF 'city1' 'city2' 'city3'..... - THEN 'region 1' and repeat for the 7 other regions.
Can you help me please?
I've been searching for a solution for hours and starting to think that the fastest way would be if I manually do all 18.000 entries :(
Many thanks in advance!
Vila
Hi Vila,
It's definitely not a trivial task :) To make things easier for us to understand, can you send a sample sheet and expected result to support@ablebits.com?
Hi Svetlana,
How do I do this? :
I've given cell N2 a name("myRef") which contains "B5". I wanna be able to write sth like "=2 * DEREFERENCE(myRef)" instead of writing "=2 * B5". What can I write to DEREFRENECE that cell's value?
Svetlana,
Thank you for this post! Very informative and I agree that too many people just rely on LOOKUP functions instead of these two. However, one thing you appear to have left out is when using this with text strings there is a drawback. Special characters break the MATCH function. For instance, if you are matching on a bunch of reason descriptions and one of them has a "/" somewhere in it the MATCH function fails while the VLOOKUP will still match it. This happens even with exact matches.
Thank you,
Alex
How To I Find A Value From A Specific Columns Value?
Ex: Column A1=1, A2=5, A3=10 And Column B1=2, B2=10, Column B3=15. Now I Find Number=10 From A1,A2,A3.
USEFUL
hi there, i believe i can do what i need to with this, however, i'm running into a problem getting it to work out.
I have spreadsheet A and spreadsheet B.
I am comparing spreadsheet A cells C1-C387 for matches against spreadsheet B cells C1-509 and when there is a match I want to return the value of spreadsheet B column D for that particular row/match and have it populate spreadsheet A column D
Does this make sense? Thanks.
Hello,
I need some advice. I have a very large spreadsheet which hundreds of lines where I need to pull information from one column, based on the information in another.
So far I can determine whether the initial column contains text from the range needed using =SUMPRODUCT(--ISNUMBER(SEARCH(($Y$2:$Y$30),A2)))>0 this is good as it identifies the list of rows which are affected, but means that i have to manually pull the information needed.
How do I go about searching a cell for the contents from a series of others and if it is found returning the found information?
Column A "Column B
does Column A contain one of these?" If column A contains data from column B, put the data from column B here
1.0_1234_text text 1234 ??
1.0 5632_text text 7895 ??
1.0_7895_text text 5278 ??
1.0_3568_text text ??
1.0_5278_text text ??
I want to show year-to-date performance for sales people using data from monthly spreadsheets to give me year-to date figures. I used the following:
=INDEX(MONTH1!C$16:C$29,MATCH($B16,MONTH1!$B$16:$B$29,0))+INDEX(MONTH1!C$16:C$29,MATCH($B16,MONTH1!$B$16:$B$29,0))
and so on for 12 months. It works great for all sales people who have been here all year. However if the salesperson does not appear on one or more month's sheets I get the dreaded N/A. Please help :)
Thanks
Hi Experts,
My requirement is : I have two excel sheets with 100 rows in sheet1 and 800 rows in sheet 2. I want to compare the sheet1 data whether is it there in sheet2 and if available that should highlight else ignore.
Please advise if we have any mechanism to achieve this. Thanks for your inouts in advance.
I have a formula of{=INDEX(B236:IN287,MATCH(B7,B237:B287,0),MATCH(B9,B236:IN236,0))} which works up until the 86th column but then returns an N/a when trying to find an answer in the 180 columns to the right.
is there a limit to the number of columns that can be used? I have checked that all is well within the table, no merged cells no numbers as text etc etc
im stuck as to why it doesn't work if you are able to shed any light please?
kind regards
Damion
There are two sheets with the data, I have a requirement to check a column in both sheets and if there is any match then the entire row has to be replaced by the matching cell in the excel.Thanks
Dears ,
please tell me that , by using index/matching how we can find a specific range from a big reference of data ! . I'm having difficult using v lookup because of many raw and columns .
please .
WOW, Thank you, thank you, thank you a million times. It worked.
Thank you for a great tutorial! I was able to get INDEX/MATCH working to auto-fill several different sheets in a document. Thank you for saving me from copy-paste!
Hi Svetlana,
I work with a spreadsheet in Excel that is the product of a CSV export from a web application. The export consists of a number of work tickets, each of which contains, among other things, a field with multiple values separated by commas. The CSV export results in one row per ticket, and it unfortunately creates a separate column for each of the comma-separated values that in reality are part of a single field. I never know ahead of time how many comma-separated values will be contained in the field in each ticket, so I never know how many total columns will be generated in the export, nor how many of the columns will contain a value for any given ticket. What I want to know is the number of values for each ticket.
For example, lets say in each ticket there is a field named "label". For ticket 1, label="car, truck, plane, bus, ship". For ticket 2, label="plane, train". The CSV export will contain five columns with the word "label" in Row 1. Row 2 will have five label values, one value in each of the five columns. Row 3 will have label values in the first two columns, and the remaining three will be empty.
I'd like to add a column with a formula to tell me how many values exist for each ticket. In the above example, the column would look like this.
Number of Labels
5
2
Can you help me with a formula for this?
Any help is much appreciated.
Dave
Hi Svetlana,
I need your help.
I have a table with 3 columns contaiin Location name, Latitude and Longitude. I want to find a lat,lon for given location from above table. I used following formula to obtain it.
INDEX(N3:N416,MATCH("Anuradhapura",M3:M416,0))
The problem is lookup_value which is location name creates while joining 4 cells. Instead of writing location name within brackets, I want to give it as a function.
Then above formula does not work.
INDEX(N4:N417,MATCH('S (1)'!B550&'S (1)'!C550&'S (1)'!D550&'S (1)'!E550,M4:M417,0))
please help me to overcome this problem.
Hi Roshan,
To help you better, we need a sample table with your data in Excel and the result you want to get. You can email it to support@ablebits.com. Please add the link to this article and your comment number.
Hi, can someone help me, I have spent 3 hours trying to find what formula I need.
I have 3 columns, 550 rows of data. Column A contains 25 options, Column B contains a 22 options for each of the options in column A, column C contains a number.
I have done 2 dropdown lists for Column A & B. what i need is a formula to look at what i have selected in the cells that have the list and bring in the number from column C.
Please help, Thanks
Hi Collin,
You should use the following array formula:
{=INDEX(C1:C550, MATCH(G2&H2, A1:A550&B1:B550, 0), 1)}
The dropdown lists are in G2, H2.
To enter this formula press CTRL+SHIFT+ENTER.
Hi Svetlana,
I need your help. I am in Recruiting.
In recruiting a potential candidate would pass through different stages (sourced-> not suitable/reject/voice message-> submitted-> interviewed -> offered-> hired).
The current ATS displays a single candidate at multiple stages (which gives me inaccurate report)
What I need is to be able to pull proper report with total unique sourced candidates, not suitable candidates, voice messages etc.
I need your help, if it is possible to have excel create a hierarchy system, where excel can pick one option (Interviewed), in case 2 or more options available (sourced, interviewed, left voice message etc).
And this needs to be multiple criteria, as I would be lining them against different job IDs.
Thanks
Subash
Hi Subash,
Please show us how your data looks like.
Good day Svetlana
I have data in several columns, the most important are 6 columns
1.Each set of data has unique number in column 1 (unique number start from 1 to …)
2.Each unique number has a set of rows with identical description in column 2 (Comm. Payable x 2 rows, Funding Charges – this can be multiple rows, Trade Payable or Trade Receivable)
3.Each unique number amount of items in column 3 (amount in only 1 row, same row Trade Payable or Trade Receivable from column 2)
4.Column 4 and 5 are buying price and selling price respectively – on same row with Trade Payable or Trade Receivable from column 2
5.Column 6 has profit & loss. Each description in column 2 rows has a value in this column
# Description Items Buying Selling P/L
86 Comm. Payable 0 0 -5
86 Trade Payable 709 0.485 0.439 -32.61
85 Comm. Payable 0 0 -5
85 Trade Payable 1740 0.345 0.315 -52.2
85 Funding Charges 0 0 -0.06
86 Funding Charges 0 0 -0.04
85 Funding Charges 0 0 -0.06
86 Funding Charges 0 0 -0.04
86 Funding Charges 0 0 -0.03
86 Funding Charges 0 0 -0.05
86 Comm. Payable 0 0 -5
85 Comm. Payable 0 0 -5
I want to change the descriptions in column 2 rows to be column headers, in a way that each unique number will now have only one row with summation of descriptions
# Buying Selling Items Funding Charges Comm. Payable P/L
85 0.485 0.439 709 -0.12 -10 -52.2
86 0.345 0.315 1740 -0.16 -10 -32.61
Thank you
Ron
Hi Ron,
Please try to do the following:
1. Add the additional column G in your table with the following formula:
=IF(COUNTIF(A2:A13, "="&A2) = 1, A2, "")
Now you have only unique values for column A in column G.
2. Add the additional columns with the following formulas:
=SUMIF(A2:A13, G2, C2:C13) - to sum the Items column
=SUMIF(A2:A13, G2, D2:D13) - to sum the Buying column
=SUMIF(A2:A13, G2, E2:E13) - to sum the Selling column
=SUMIF(A2:A13, G2, F2:C13) - to sum the P/L column
3. Remove the blank rows to get the results.
Hello Svetlana,
Your tutorial is absolutely great. Thank you very much.
Hi all,
i need to sort the B no. column datas with respect to the A no. column datas, (both columns are text format)
Hi Jinson,
Please try to do the following:
1. Select the range with your data - A1:B100 for example.
2. Select the Data ribbon tab and click the Sort button.
3. Select the Column A in the Column.
4. Click OK.
I want formula for following condition.
If age >22 then Basic*60% else if age =16 and less than 22 then basic*50% and if age=11 then basic*40% and if age=6 then basic*30% and age<=6 then basic*20%
Regards
Rajaraam
Hi Rajaram,
You should use the following formula:
=IF(A1>22, B1*1.6, IF(A1>=16, B1*1.5, IF(A1>=11, B1*1.4, IF(A1>6, B1*1.3, B1*1.2))))
The age values are in Column A, the base values are in Column B.
I want formula for following condition.
If age >22 then Basic*60% else if age =16 and less than 22 then basic*50% and if age=11 then basic*40% and if age=6 then basic*30% and age<=6 then basic*20%
i don't know if this is relevant, but i have 5 cells that all have a "=IF" function in so it will look at a cell and return "TRUE" or "False" value.
what i want to do is, have it look at all 5 cells that were returned and number for the "TRUE" cell
cell values are
A2 = False
B2 = False
C2 = True
D2 = False
E2 = False
then i want it to look at all 5 cells and return a number for the "TRUE" cell so the result must be "3" for this example
the numbers that will be assigned to the cell will be
A2 = 1
B2 = 2
C2 = 3
D2 = 4
E2 = 5
how would i do this?
Hi Marius,
You should use the following formula:
=MATCH(TRUE, A2:E2, 0)
Is there a way to autofill the array INDEX/Match Formula? I've created the formula in row 4 and need to drag it down and autofill though 2000 rows. Thank you in advance!
Hi chris,
Please look at the following article, it should help:
https://support.office.com/en-us/article/Fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db
Using this array,
=LOOKUP(2,1/(Z$18966:Z$21192=G16930),AA$18966:AA$21192)
is it possible to return the value?
Thanks
Hi Ronell,
To be able to assist you better please describe your task in more detail.
Can I return the value "" using this formula
=LOOKUP(2,1/(Z$18966:Z$21192=G16930),AA$18966:AA$21192)
Thanks
Dear Svetlana. Thank you very much for you explanations.
I have a qyuestion to you, taking the same data table in your explanation.
I would like to now how to handle INDEX/MATCH in the following case:
Below each product and in the same line of the customer, I would like to have the $ sum
Products Lemons Apples Sweets Pies Biscuits
Customer ___________________________________________
Dan Brown ___________________________________________
Jeremy Hill ___________________________________________
Romert Acey ___________________________________________
Tom Boone ___________________________________________
Thanks
Carlos
Hello, I'm sorry, I manage to see my mistake in writing the formula, and make it work.
Thanks anyway
Carlos
Good day Svetlana,
I downloaded the examble 'index-match-multile-criteria'. It works beautifully, but when I use the same type of argument in my sheet it does not want to work. I have tried and changed and even typed in the formula with the exact spacing etc.. Nothing!! Somewhere I did something wrong. Please have a look and help me out!
Hi Lida,
Please send the sample workbook to our support team (support@ablebits.com) or upload it to Excel Online and post a link here, and we will try to help.
Hi i currently using index and match function in excel, to auto match and index the value back, if i put a set of new data to compare with my original data.
But however sometime a new data had a multiple result , how to i auto insert a row on the original data columns for the multiple result?
this is my formula code : =INDEX($L:$L,MATCH($A:$A,$K:$K,0)) it could match and bring back the index value back i wanted ... but those with multiple result it only return back to the first result it show.
Hello.. I now actually trying using index match/IF function to try to index the value from "ColE" onto "ColB" , by matching "ColA" compare with "ColD".
For my eg. Shown below "Urr" got different value in "ColE" how to I insert the three different "Urr" with the different number
result in "ColE" onto "ColA" with the same exact result number should appear in "ColB"???
And "colA" "Usw" data still match with "ColD" "Usw"?
Eg.
ColA
Doman
Urr
Usw
ColD ColE
Doman 1234
Urr 12345
Urr 12345
Urr 12346
Hello.. I now actually trying using index match/IF function to try to index the value from "ColE" onto "ColB" , by matching "ColA" with "ColD".
For my eg. Shown below "Urr" got different value in "ColE" how to I insert the three different "Urr" with the different number
result in "ColE" to "ColA" with the same exact result number should appear in "ColB"???
And "colA" "Usw" data still match with "ColD" "Usw"?
Eg.
ColA ColB ColD ColE
Doman Doman 123
Urr Urr 1234
Usw Urr 12345
Usw 222
Hello.. I now actually trying using index match/IF function to try to index the value from "ColE" onto "ColB" , by matching "ColA" with "ColD".
For my eg. Shown below "Urr" got different value in "ColE" how to I insert the three different "Urr" with the different number result in "ColE" to "ColA" with the same exact result number should appear in "ColB"???
Eg.
ColA ColB ColD ColE
Doman Doman 123
Urr Urr 1234
Usw Urr 12345
Urr 123456
Usw 222
Hi,
Please help..
I have a storage tank calibration shown below. it has one column is height and the the corresponding volume on the next column. The table has wide range of cell. columns were height-volume-height volume and so on. If the table has only two columns then I will use =vlookup(value of I want to look up,range of cells table,the column number in range containing the return). Now since the height and volume is arrange in more columns how will I get the right value of I want to look up?? Please see sample.. Thank you
height volume height volume height volume
cm. lit cm. lit. cm. lit.
1.00 107 41.00 38244 82.00 77620
2.00 579 42.00 39204 83.00 78580
3.00 1325 43.00 40164 84.00 79541
4.00 2230 44.00 42125 85.00 80501
5.00 4149 45.00 42085 86.00 81462
etc...
Regards.
Mike
Hi,
I want to use a MATCH function as follows:
=MATCH($B2,$B3!$A1:$A10,0)
Here, the values of the cells B2 and B3 are dynamic. I have different sheets, for which I specify the name in B3 cell.
Can someone please suggest a solution for my requirement.
Regards,
Ravi.
hello
Dear Svetlana,
If i have three condition can i make the formula like :
=INDEX(Sheet1!$G$2:$G$71,MATCH(C$4,Sheet1!$C$2:$C$71,0),MATCH($B6,Sheet1!$E$2:$E$71,0),MATCH($A5,Sheet1!$D$2:$D$71,0))
Dear Svetlana,
three days ago I stopped on your website and found very useful information about excel.
I tried to find solution for my case(VLOOKUP,INDEX MATCH)but without success.
Maybe you can help me ?
I have two tables. First table which is in one sheet has 2 columns(1.ident number of product and 2.quantity).
Second table which is in another sheet has also 2 columns(1. ident number of product and 2. price)
Numbers of products(in raws) in first table are larger than the numbers in second table and first table includes all products(raws) from second table.
How I can multiple quantity and price from theese tables ?
Best regards,
Krešo.
hello
I need help, I have 3 different suppliers for same products with different Artikel number and product name but with the same barcodes.
Some of the product is cheap by the supplier number one but some the products are cheap by the supplier number 2 or 3.
Now my question is this: Each supplier have more than 2000 products and it is very difficult to go one by one to find out who is selling for a good price and which product from three different suppliers.
Hi,
I have a Table 'Project Register' and within it I have 3 fields/columns - Client, Program, and Project. There are also fields set as named ranges for the above, using a validation formula to return valid options (working fine).
My issue is the below formula returns NA# instead of an array of row numbers where the fields are blank, or when they match one or more criteria. (IE; All = ""; Program and Project are blank when client is selected; OR when Client and Program are selected, but Project is blank)... I can't see where the formula has gone wrong. Evaluation step through shows ok to the point of returning the row numbers. Your assistance is appreciated.
=IFERROR(INDEX(Project_Register[Project Name],
IF((Client="")*(Program="")*(Project="")),1,
IF((Project_Register[Client]=Client)*((Program="")*(Project="")),1,
IF(((Project_Register[Client]=Client)*(Project_Register[Client Program]=Program))*(Project=""),1,
IF((Project_Register[Client]=Client)*(Project_Register[Client Program]=Program)*(Project_Register[Client Project Name]=Project),1,0)))),ROW(Project_Register[Project Name]),0),"")
Very helpful, thank you!!
Clarification: the formula I posted above references column "B" multiple times. That is actually on another tab in the spreadsheet and is where I'm placing the results of the formula.
I'm trying to create a unique list of names, from two columns containing names, but only include names that fall between a date range. Example, column A has dates, column B has a name, and column C may (or may not) has another name. Given a Start date and an end date in some other cell (say in J1 and M1), is there a formula that will return a list of unique names from both columns B & C (same names may be included in both columns, but are never the same on the same row), that have a date that is within the given date range? I've been able to get the unique list of names using this formula ("Salesperson1" and "Salesperson2" are named ranges equivalent to columns B & C in my example above):
{=IFERROR(IFERROR(INDEX(Salesperson1,MATCH(0,IF(ISBLANK(Salesperson1),1,COUNTIF($B$3:B3,Salesperson1)),0)),INDEX(Salesperson2,MATCH(0,IF(ISBLANK(Salesperson2),1,COUNTIF($B$3:B3,Salesperson2)),0))),"")}
Any help would be appreciated.
-20%
-25%
-27%
-23%
-19%
As above I have a number of values and I want to reduce an array of values say by 7
The result becomes
-13%
-18%
-20%
-16%
-12%
What formula can I use in excel to get this value ?
Hi
I have two columns in a file. Date (Column A) and Amount (Column B)
Date Amount
8/22/2016 600
8/23/2016 600
8/24/2016 200
8/25/2016 800
8/26/2016 600
8/27/2016 600
8/28/2016 600
What I want to achieve is described below.
In another sheet, I have certain dates. Not all the dates from above, but specific dates as below. And what I want to achieve through a formula is what is under column Sum.
Dates Sum
8/23/2016 =600+600
8/25/2016 =200+800
8/28/2016 =600+600+600
I'll explain the formula I want taking 8/28/2016 as an example.
I have calculated the sum until 8/25/2016. So I want to look at the data
from the next row after 8/25/206 until the date specified here, that is 8/28/2016 and then sum it up.
If I take 8/25/2016 as an example, then I have calculated the sum until 8/23/206. Hence I would have to pick up the numbers from the row below 8/23 (from the data) which is 8/24 until 8/25 and then sum that up.
Can someone please help me with this?
Sorry, I figured it out!! :-)
=SUM(INDIRECT("Attendance!E" &Match(A22,Attendance!$A$1:$A$842,1)& ":E" &Match(A23,Attendance!$A$1:$A$842,1)))
Cell reference here doesn't really match the example data I have given above, but you will get the concept
Correction, you need to add a + 1
=SUM(INDIRECT("Attendance!E" &(Match(A22,Attendance!$A$1:$A$842,1)+1)& ":E" &Match(A23,Attendance!$A$1:$A$842,1)))
Hi
Thanks for this article. I want sum of all results that are there in selected data from index match instead of single result.
=INDEX('LR CashFlow'!$B$2:$F$561,MATCH(1,('LR CashFlow'!$B$2:$B$561='LR CALC SHEET'!B8)*('LR CashFlow'!$F$2:$F$561='LR CALC SHEET'!$D$6),0),3).
Can you kindly help me in this.
Thanks