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 11. Total comments: 614
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
Hi I am trying to find a value in Column A and I know the value in Colum B.
For example, I have placed a value from Column B, cell B10 in a parallel sheet. next to that I want to place the value of column A, cell A10. can I please have any advise on this ?
Please do not respond to this , I have reached the solution !
Thanks :)
My question is, is there a way I can create a formula that will pull out code words from a variety of words that are listed in a cell? I have a column in which notes are listed but I would like to be able to find trends by using a formula to count key words listed within the notes.
Please help. I want that the result would be like this:
RESULT: Cell A4 is Letters with "No". Cell A5 is: B,C,F,K
DATA:
Cell A1 is A, B1 is B, C1 is C,...up to Q1 is Q. Cell A2 is Yes, B2 is No, C2 is No, D2 is Yes, E2 is Yes, F2 is Yes, G2 is Yes, H2 is Yes, I2 is Yes, J2 is Yes, K2 is No,...up to Q2 is No.
Dear Svetlana,
My Data Table Is Like :
From Per To Per Marks
-40.01 -999 0
-30.01 -40 20
-20.01 -30 30
-15.01 -20 40
-10.01 -15 55
-8.01 -10 70
-5.01 -8 90
0 -5 100
0.01 5 100
5.01 8 90
8.01 10 80
10.01 15 65
15.01 20 55
20.01 30 45
30.01 40 35
40.01 999 15
I have Value Like :
Per Marks
-20.83 ?
10.53 ?
13.64 ?
-3.35 ?
1.63 ?
32.14 ?
8.33 ?
6.67 ?
2.08 ?
2.00 ?
8.33 ?
20.19 ?
-6.45 ?
3.27 ?
-2.17 ?
0.00 ?
28.35 ?
0.00 ?
I want Answer On "?"
I want to look "Per" in upside table.
Plz Give me formula for this
Dear Svetlana,
I wonder if you might help with a problem…I’m not sure what I’m trying to do is possible.
I have a ‘Lookup’ worksheet with a simple three column setup
A B C
WARD TRADEAS CPAddress
1 ERNEHL Shop 1 Address 1
2 PLAINS Restaurant 2 Address 2
3 BSTALB School B Address 3
4 GEDLNG Shop 2 Address 4
5 CARLTN School B Address 5
6 BSTALB Shop 3 Address 6
7 NETHFD Shop 4 Address 7
8 NETHFD Restaurant 3 Address 8
9 NETHFD Shop 5 Address 9
10 PHOENX Restaurant 4 Address 10
I then have officers that cover particular ward(s)…so:-
Officer A covers PHOENX, PLAINS and ERNEHL
Officer B covers NETHFD, BSTALB, GEDLNG and CARLTN
What I was trying to do was to search the ‘Lookup’ worksheet for all establishments assigned to Officer A (i.e. those with a WARD code of PHOENX, PLAINS and ERNEHL) and then return the resulting list of establishments and addresses (Column B and C) into a second worksheet (so giving a list of establishments to be inspected)…and them similarly repeat this for Officer B.
Any pointers as to how I would achieve this would be really gratefully received
Many thanks
Brendan
I appreciate it if somebody help me:
I have two different excel files X and Y. In file X there are column "Date" with format of ("e.g January 1,2012") and column "Time" with format of (" e.g 1 a.m to 1:59 a.m") . in file "Y" I have three columns: "Date"(same format of file X) ,"Time" (format of 1 a.m that means 1 a.m to 1:59 a.m) and column "Amount". I want to first compare column "Date" in two files and see if they are matched then compare their appropriate "time" and see if their time are matched, then bring the appropriate "Amount" from file "y" to file X in a new column. note: time format in two files are different.
Very useful information.. Thank you for sharing
Hi, got a simple question. In your example of "Look up with multiple criteria using INDEX MATCH," the customer/product/qty example, how would you write a INDEX MATCH formula to average the Biscuits quantities?
Hi Jason,
I believe it's easier to do with the AVERAGEIF formula like this:
=AVERAGEIF(B2:B10, "biscuits", C2:C10)
Where B2:B10 are products and C2:C10 are quantities.
hi !
i need tot know How to VLOOKUP with Multiple Criteria in different sheets if A1 = A:A & B2 = B:E,3
Tnx!
THANXXXXXXXXXXX.......FOR INDEX FORMULA
Hello! I need to visually split "Pyramid" chart. I created one for one fiscal year but I need to have 2 fiscal years reflected on one pyramid with a vertical line in the middle. Can anybody help/show hot to do this?
where I can send "snap-it" attachment?
Thank you! :-)
Instaded of using such cretical formula we can get these results just using Pivote table function " so could I know what's advantage of using Index & Match function over Pivote table"
I'm having difficulty getting any of this to work for exactly what I'm wanting to do. I'm working with 2 spreadsheets: "2001 Base Data" (BD) and "Knowledge Occupations" (KO). I'm trying to pull data from BD into KO. In KO I have the Name in column D. In each successive column there is an OCC code (xx-xxxx). I'm trying to pull employment by matching the Name in column D and an OCC code with the name & OCC code in BD. BD has the name in column B and the OCC code in column C. I want KO to return the value from column F in spreadsheet BD.
Any guidance that can be provided would be immensely appreciated.
Thank you!
Dear mam/sir i reading in vlookup formula but i did not clear so kindly that by example
Hello Pramod,
You can find the detailed explanation of VLOOKUP with formula examples in Excel VLOOKUP tutorial.
Hi there hope you could help me i have a problem to use index match formula i before i was using vlookup but each time i have pasted new report the formula stop working
i have a tab with vendor list
column A Document number
Column B Vendor number
and tab with vat input
column A
document number
i need formula to go to vendor list match the document number with the document number from VAT list and return the vendor number from the vendor list which is next to the document number in this list.
My formula = INDEX( VENDORLIST $A:$B, Match(VAT List $A2,VAT List A:A,0),2))
this formula does not return the right vendor number.
Can you please advise ?
Thanks,
Aleks
Is there a way for me to copy the array formula to multiple cells without press ctrl + shift + enter at each cell? when i press shift+F9 an error appear "Excel run out of resource while attempting to calculate one or more formulas. as a result, these formulas cannot be evaluated".
I am trying to set a spreadsheet so that if it looks up a table. There are three columns; size of timber, rate to charge and the rate charged in £.
I want to set it so that if the size and rate match on my spreadsheet details I have inputted then it picks up the charge rate from the table and inputs it so I know what to charge based on the size and rate chosen.
Help if possible!
what are the scenario where we do use index and match?
and what is If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. In Match function
Hi,
A big thanks to anyone who can help with this.
Column A
14P3WSKK1637I35DM
13A4P5FL7243G95126
13A4CL1E5051I36B28
13A4CL1E5051I09Q30
13A4Y9GE50C733N32
13A4DLES1206I90132
14P4CWN5V3W5G24PS
14P4CWN5V5Q5G24PSS
Coumn B
S
SS
28
30
32
M
Comumn C
Small
Extra Small
Size 28
Suze 30
Suze 32
Medium
I have to search for the text from column B in Column A and return the corresponding value from Column C.
Basically the last character or combination of the last two characters make up the size of the clothing.
Thanks a million.
Umar
Svetlana, I enjoy your formulas explanations: very visual, very concise.
I have a long formula I am using (was created my may manager). Formula works, but I'd like to find ways to make it shorter and more"elegant". Since the formula takes almost a page in Word, I'd like to pay you for a working solution. Must be for Excel 2010 & 2013. I use both for internal reasons on 2 computers.
Thank you!
Hi Olga,
I am glad to hear you've found my articles helpful. Please send us a sample workbook with your original formula to support@ablebits.com and we will see if we can help. (To make sure your message immediately gets in the right hands, please refer to this comment or write something like "Att: Svetlana" in the subject).