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 16. Total comments: 614
Hi,
I want formula for its like a inventory style, I have received same item different date and different price than I want issue same price what I was received
Example:
Received:
Date Description UOM Qty Rate
01/12/2015 Boat Nail 4" Kgs 200 10
Issue Or Sale
Date Description UOM Qty Rate
01/12/2015 Boat Nail 4" Kgs 20 10
Issue Sheet Date,Description Qty, rate Placed placed automatically compare to received sheet Date,Description and Qty
Hello,
Merge Tables Wizard add-in can help you with the task. Please find more information about it on this page:
https://www.ablebits.com/excel-lookup-tables/index.php
Not working for a situation like this
Lookup value :SSJQFIDPK
From array
MVKLALQPRDSVARIAREHDINDNLLFKWLRIWLIKGRISRRLPLTNSSGIGVELLPVEMTLDERPYPVFTRGNYEDQ
MLTLEVIRKINDWPSNRVHELLPWNLSAVK
MTTLHYYLKEALLNIIENRRQNFAFLVFLSLSFIGIIITDSLIYSVSLKAEEELKVHSDKVIFVKLYRPKTVGYITEKFITVSKVLSFSKNAFLYVSDTPFSGELFSVSGIDKLGLNTEYSGDLNDKYNGNVAIVNESSPFFSKKQIFINGVPFKIIGVRLNSKTDFLDSLGLKANQSDEHIFIPLETMFKMKLDNRVNAVKIFLDNIVTKRDINNVKRVLYDNDIRKFDIVTSLNAKEAVDRVLERFSLLTNSVYVILTLSASVTCFILSKRSFYSRRVELSLKIIHGTEKKEITVLIIIESLIMLSVCLFISLIHAGVIMHIIKYFLDVTISIRTTMITISLANVLLVFIFANIIFSRLFFSINPVNAIKGKIE
MRHILYSFLAINAYLFSTQTLAKDCIIDNFFQKSIQFNSYSLDIEELDINKHNNIKTMLPDINIGLGQYINNNQWFSSITDSHFYLSLSYNLLSAYEAKMQNNKLDIANYLKYIEMLSERNNYIINLFSEIINYKIKKSHLMLMFERYRKLNKEYEIAKRKMSIGLISVLDVEMIYNILQKIRFDIDVLEEEESLLSDKISREYHVPESAIPDITYHKLKECKTADFYTLLAENKKLKIKAADIDNDIRKLSEIPSFYLSFGLTPKQGGALGNMSLRKMDYSASLGISFPLMGLFSSSVNQKEKIISMSRTRNESLKENIKLDLLEKEIRQKIDRLEKNLAMMKNELTLKKRKVEYINYRLKNGQDDVISYLSSVENLHETENEFQKIGYEIEYYSLYHYFLLQHISNTGEM
MIEGNVVTVERKLFSVLNHEYTAKKDIVAMKRNMEEKKLARLKGVKTHSTSMFSKGLISRESLHDIDEKISNTELTIMGLDIESKNLEQLLKLSSPFLHTPFIIRNIFVTNEQYVNAGDDIMSVELLDNFYIDVKFDPVSITGNIRDKRIRYRSLVNSLMGAATVVKNIRASGESTQGEDTSGLRSITLLIDGDRNELSNLLDTAFEIIIDD
MIRVKIHKKPIENRTILNNSTIEIKEGSFNIITGPSGVGKTSLLNIIGLLDNAFVGEYEFFGKKVEIKDNSITTYIRRKYFGFIFQDSLINVKQNVLRNILCSVDSQNIIAARERINDILVSVGLSNINNNVSFLSGGEKQRLALARALIKKPSILLADEPTASLDIKNKKLVMNILSEYNNQGGTVVMVTHDLELIDENMTLIQLLNT
MKFTIVLLYFFAYYLAARKRRVSLFFTILLYSIIFSGMYFSSGFLEYYGSSNLYLSFGLLCYNMITLVIYGFLSSYGVLGACLHALLLTSLSAFGMFIPLNPLIVLYYDFPDILPRTDIPVLNLLILNLIPAVTFSLKISFFLRSLMLLLFPLIWKTPVNITHPPLNIVIVQVGLYFKKVGVRGNFYTDLNEFVRNKKVDLVILSENVFFGYKNDYIKERTKHLLKQLKDNRFHYKYGILMNLYGYQDINNVVSAFWHKEEFLLHQKSKLIPFFEKKSFYNSPEPSTSPFLYYKKKYNEQDILDFNNIKMSIHICYEGLFPEGESRRKDISIVQSDYSWLSDNHKYDNTLINGSILSKFSVSPNTPLINIQNYGGTVLIDKNWKIDMDLFNRSKTEPFLFTQI
MHSENIAAYIGLDVHKETLAVAIAAPERLGEVRYYGTINNEAQAVRRLFQKLQGLYGNILSCYEAGPCGFGLYHQLTAMNIKCQVIAPSRIPKSPTDRIKNDHRDAISLARLLRAGELTPVWIPDLTHEAMRDLIRARAAAKRDSRVARQRILSMLLRTDKRYAGKHWTGKHRTWLANQSFSQPSQQIAFQHYCQSLEQIEDRILQLDQKISRLLPEWSLCNLVCQLQALKGVGQLIAITLVAELGDFSRFSNPKQLMAFLGLVPGEYSSGNSIRPRGITKVGNSELRRLLYEAAWSYRTPAKVGAWLIYYRPDSVTQYSKDIAWKAQQRLCSRYRSLTAKGKNHK
Hello Nazrul,
It looks like this sequence of characters is not present in the array.
Hi, I'm trying to find a formula that would help me solve the following problem:
Code Country LE#
123 US 4026
123 UK 4026
123 US 3026
435 CN 1419
435 CN 1398
I need to find a formula that can return lowest LE# so if I look up 123 & US it should return with the lowest LE# which is 3026
Thanks.
Hello, David,
Please try this array formula:
{=MIN(IF((A2:A6=123) * (B2:B6="US"), C2:C6))}
Make sure you press CTRL + SHIFT + ENTER after entering it.
Thank you!
Hi Svetlana,
I am trying to apply below formula to know the date of particular event corresponding to the person listed in column B, but it resulting as #REF!.
INDEX('Bill Entry'!$K:$K,MATCH(sheet5!$B6,'Bill Entry'!$B:$B,0),MATCH(sheet5!D$3,'Bill Entry'!$H:$H,0))
also used the following, which shows #VALUE!
INDEX('Bill Entry'!$K:$K,MATCH(sheet5!$B7&sheet5!D$3,'Bill Entry'!$B:$B&'Bill Entry'!$H:$H,0))
Pl. resolve it
Amod Ranjan
Hi,
Is it possible to do the index match function with an isblank formula?
The index match formula works great, but returns a '0' when the cell is blank, I would rather it returned a blank cell.
Many thanks
Emma
Hello, Emma,
Here's a sample formula that may work for your task:
=IF(ISBLANK(INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))), "", INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0)))
Hello Svetlana,
If in above table of country population; one country is e.g. Italy is 5 times written together with other countries, then how to get minimum population of Italy from such tables???
Thanks for your kind guidance.
For I am no expert, and have to translate all formulas (No, I dont work with a English version of office) some of the listed may be inacurate/wrong.
But i am pretty sure that:
- under "Why Excel's INDEX MATCH is better than VLOOKUP", 3.
=INDEX(D5:D10, MATCH(TRUE, INDEX(B5:B10=A2, 0), 0))
should be more like
=INDEX(D5:D10, MATCH(A2, B5:B10, 0))
- under "Calculations with INDEX MATCH in Excel (AVERAGE, MAX, MIN)", first table
Beijing has MAX population, and Lima MIN population, table states just the other way.
- under "How to use INDEX MATCH to search by row and column values", right above second graphic
=INDEX($A$1:$E$11, 4, 5, 0))
should be
=INDEX($A$1:$E$11, 4, 5)
If you copy&paste and then replace subfunctions with values, make sure you get the whole subfuncttion...or does the 0 do something I dont get? (despite the second closing bracelet)
- Look up with multiple criteria using INDEX MATCH, first graphic
just a minor thing: Why no row numbers? Really would help. :)
I only red on 'till (and including) "Look up with multiple criteria using INDEX MATCH" for I didn't need the following for solving my problem.
Pls, pls, pls proofread your articles before releasing. I understand that's a pain in the neck, but honestly, If I wouldn't know something about excel in the first place, I would have quit this article with a "fuckoff author" thought by the first non working formula. I came here to find a solution for my problem/learn something new. And if you, the "teacher", give me an example and that doesn't work, what am I supposed to do? Even I took a few minutes to realise that this is not just a new feature but an error, for I generally trust the internet.
Greetings
The Cook
PS: I hope these are mistakes. Or I just made an donkey out of myself.
PPS: "...for I generally trust the internet." even Abraham Lincoln knew: "Do not trust every (quote) at the internet." Wise bloke he is, isn't he?
Summary: A few errors and one cosmetic sugestion I found in this article.
The Cook is back: Just a comment for the friendly fellow who will write a comment about how someone else allready wrote a comment about this "mistakes": Here is a penny for your thoughts.
If that's true, just correct it allready. I only checked the first few comments, and realized most of them are questions for szenarios they have. And there are more comments that article. Writing my two comments was faster for me than checking the comments, for I read the article anyways 'till a certain point.
If that is not true, and I am indeed the first to point out mistakes:
I totaly checked all other comments, and couldn't find any pointing out mistakes. So i sacrificed some of *my precioussss* time to fulfill the heroic deed of doing so.
And I am no mother-toungish-english...er? so keep all tiping mitstakessd you find. :)
Summary: Some gibberish, some justifications, no real information.
Read for pleasure. Or don't.
Hi, i need an help on index match function! i have a set of data for which i want to use index match function. My problem is i have lookup value but my return value also there in same column in the lookup value. how to return the below value.for example :-
Sam
age41
Ram
age32
height 6"
Mary
Age7
So i have all the name but i need the below value from the name which i have already done using index match function but i cant. Can u pls help me out.
I've seen your article and I'm impressed on your. Where is my question:
In the population table I've to find out the name of the country having highest population.
Please help me out how to do that without macro or VBA
Hi Fellows,
I was wondering whether someone can Help.
Is there a way to use the Index function to do the following.
Check for a Match (let's from A2 in column E) and if no match is found look for a match (let's say from B2 in I) and output the value from the column?
Thank you in advance for the cooperation.
Regards,
Slavcho
Hello Svetlana,
I wanna use index match formula to grab the first matching value"1" in every row and seperate it from the rest of the data. However most of the formula solutions return a report fromat however I wanna keep the same table design but just have it in two copies so that I can create a pivot afterwards.
I formed an example below. Thanks so much in advance.
2004/1 2004/2 2004/3 2004/4 2004/5 2004/6
Product 1 1 1 1
Product 2 1 1 1
Product 3 1
Product 4 1 1 1
Product 5 1 1 1
Product 6 1 1 1
How would you resolve this in order to get the first column value:
=INDEX(O3:O12^MATCH(B25^IF(((B25=R3:R12))^0^1)^0))
Input Value: B25: 8748
0 NA NA 9000 9999
1 NA NA 0 999
2 NA NA 1000 1499
3 NA NA 1500 1799
4 NA NA 2000 3999
5 NA NA 4000 4999
6 NA NA 5000 5199
7 NA NA 5200 5999
8 NA NA 6000 6799
9 NA NA 7000 8999
=INDEX(O3:O12^MATCH(B25^IF(IF(--(B25=R3:R12)^0^1)^0^1)^0))
I've tried and am still failing.
Hello Svetlana
I am started to use Index Match function and I tried your article/suggestion very useful.
I have now looking at using Index Match with multiple criteria and I followed the example. I have noted that in this case one of the advantages wrt vlookup value use is lost, basically I can not longer insert or delete column in the lookup table. Can you please confirm that? is there 1 alternative can be use that overrides this problem
Thanks
S
Hi. An excellent article on the use of INDEX MATCH, thank you. I have added the error handler (IFERROR) but previously with VLOOKUP I could also check to see if the resulting value was blank, and leave it blank in the cell. The data type in my cell is DATE, and if the answer is 0, it formats this as a date which is a pain.
My old formula is: =IFERROR(IF(ISBLANK(VLOOKUP(A1,Endorsements!A2:L33,5,FALSE)),"",VLOOKUP(A1,Endorsements!A2:L33,5,FALSE)),"")
I now have =IFERROR(INDEX(Endorsements!$E:$E,MATCH(A1,Endorsements!$A:$A,0)),"") which works great unless the item found is a 0, in which case the cell then shows 00 Jan 00.
Hope you can assist, this has been baffling me for many hours!
Thank you
Thanks a lot to post a very useful topic which can minimize our work at a great extent.
HELLO
i have a master list of items and New items codes . and another list of secondary list of items which have old codes .
I need to find out the common item description and replace the old item codes with the new codes.
thanks
Hi Svetlana! I have a spreadsheet with NFL stats and player rankings for all teams. My quarterback rankings are on Sheet 1 and wide receivers on Sheet 2.
I am trying to add the ranking of a quarter back with the ranking of a wide receiver for the same team to return a value for the wide receiver.
Example: wide receiver Joe Smith plays for team X and is ranked #5, his quarter back John Doe is ranked 6 = Joe Smith's value of 11.
Here is one formula that I've tried unsuccessfully so far : =IF(MATCH(C2:C51,Sheet2!C2:C39,0),A2:A51+Sheet2!A2:A39). Thank you for any assistance provided.
Hi every one.
Whats the simple way to do this
* If the DATE entered in "X" sheet goes to same date cell i.e if entered 10th then its goes to 10th Row of "Y"sheet".
Thanking you in advance for your kind support
I am new to the Vlookup function, I feel I have a good grasp of it but one of my basic lookups is not working correctly. Ifyou look at test sheet, cell c24. The value is not returning correctly, there seems to be some math occurning and I cant figure out where it is. Any assistance?
C24 - =IFERROR(VLOOKUP(A24,Sheet1!A2:B23,2),0)
I was trying to post the xls but don't see an attachment option.
Hello,
I'm wondering if anyone can help me.
I am trying to create an organiser for my other half for university. Column C10,313 contains the date with column D10,313 Containing work set and a few other columns ToDo, Exams, Notes etc.
I am trying to create a formula with INDEX MATCH to create a new table with a column for 'Work to be completed' and the date set sequentially so when the organiser is being used you don't have to use any filtering functions to search the organiser. I want to keep the interface as simple as possible so you just have to fill in the 'work set' next to the date and it auto populates the concise list of 'Work to be completed' with corresponding date...
Any one know how I can go about this? It would be much appreciated.
Cheers,
Lewis
Hi
I have downloaded Microsoft Templates file called "Sales invoice tracker" which has 4 key tabs which enables me to enter Customer details, Invoice details, Invoice summary and has a Invoice template tab. The file works great for recording all client information, client sales and creates the invoice via a dropdown list named (rngInvoice in the formula below) and referenced by the chosen job number in the "invoice" template. The formula has work fine until the ROW(1:1)) reaches ROW(13:13)) down the invoice page, i have reviewed the formula and for the life of me i cannot understand how this part of ROW works
=IFERROR(INDEX(InvoiceDetails,SMALL(IF(InvoiceDetails[Invoice '#]=rngInvoice,ROW(InvoiceDetails)-ROW(InvoiceDetails[#Headers])), ROW(1:1)), MATCH($B$13, InvoiceDetails[#Headers], 0)),"")
I have recreated a similar document but i am struggling with capturing and transferring data of multiple columns and rows via a single Job number column
All i would like to do is select a Job number on the dropdown list on the "invoice" template and have the data from the "invoice details" with multiple columns and rows to be copied into multiple columns and rows on the invoice
Please help
Hi
I have a file with 2 tabs, the first one has the customers name and the quantity that they ordered by style and on the second tab I also have the customer name and the style that they forecasted, I want to match the customer name and the style of the first tab with the second tab and return the value of the cell with the forecast quantity. I'm using the below formula, but it is not working
=IFERROR(INDEX(Sheet2!$1:$65536,MATCH($A4:$B4,Sheet2!$C:$D,0),MATCH(D$3,Sheet2!$1:$1,0),1),0)
Hi
Anyone can help me to change this vlookup function to index mach?
=IFERROR(VLOOKUP("*"&G:G&"*",'ADV. & LOAN'!B:D,2,FALSE),"")
To be clearer in my last comment, "STREET" is the same as "Street", but is not the same as "Street.". (In my case, comparing addresses, " Lake St." is not the same as "LAKE ST" without the abbreviating period.)
By the way, I did some experiments and even if you pick exact match (0), it is still case insensitive when you try to match.
STREET is the same as Street.
(Now if I could get a "fuzzy" match so that Road would match RD, wow!)
could you please help me out on this scenario.
SCENARIO
IF cell D3 AND range F3:AJ3 ISBLANK(), THEN C3 =""
IF cell D3 OR range F3:AJ3 is NOT BLANK , THEN MATCH values of cell D3 with Range F2:AJ2, whichever is MAX should show in cell C3.
=IF(ISNA(OR(COUNTA(F3:AJ3)=0,(MAX(LOOKUP(2,1/(F3:AJ3""),$F$2:$AJ$2)<D3)))),IF(D3="","",D3),LOOKUP(2,1/(F3:AJ3""),$F$2:$AJ$2))
Hi Svetlana,
Can you kindly show us how we use this function between two worksheet but the excel book is same.I have tired it but i did not completely successful.
Hi Svetlana,
I'm dealing with a software that uses excel functionnalities, but can't support array functions. Do you know an alternate way of doing what an array formula does?
Thanks,
Félix
Sorry the table collapsed!
……A………………………B………………………C………………………………D……………………………………………………E
Alpha………………1234………………Here……………East Here…………………………………………2054
East Here……2054…………………………………….Here…………………………………………………….1223
Golf…………………4657…………………………………….North Here………………………………………2212
Here…………………1223…………………………………….The North Here of Little Westerly…1235
Juliet……………7511…………………………………….West Here…………………………………………5589
North Here…2212
Siera………………4651
The North Here of Little Westerly……….1235
West Here…………………………………………..5589
Thank you
Svetlana
I have a large spreadsheet that contains a lot of information about thousands of locations. Each location has a unique 4 digit index number and a name. The name may not be unique but may be used in different locations such as North Here, South Here, East Here or “The North here of Little Easterly”. Some people know the location by the number and some people know the location by simply, in this case, “Here”. A simple lookup table can be used in the first instance for the unique number but what code must I use so that by entering the name, “Here” in a field, the code placed in stacked fields (say 6, one above the other) will pull up all results where the name “Here” is contained in the text in the field and report the unique location number in the adjacent field:
A B C D E
Alpha 1234 Here East Here 2054
East Here 2054 Here 1223
Golf 4657 North Here 2212
Here 1223 The North Here of Little Westerly 1235
Juliet 7511 West Here 5589
North Here 2212
Siera 4651
The North Here
of Little Westerly 1235
West Here 5589
Thank you
Svetlana
I have a large spreadsheet that contains a lot of information about many locations.
Each location has a unique 4 digit index number and a name. The name may not be unique but may be used in different locations such as North Here, South Here, East Here or “The North here of Little Easterly”. Some people know the location by the number and some people know the location by simply, in this case, “Here”. A simple lookup table can be used in the first instance for the unique number but what code must I use so that by entering the name, “Here” in a field, the code placed in stacked fields (say 6, one above the other) will pull up all results where the name “Here” is contained in the text in the field and report the unique location number in the adjacent field:
A B C D E
Alpha 1234 Here East Here 2054
East Here 2054 Here 1223
Golf 4657 North Here 2212
Here 1223 The North Here of Little Westerly 1235
Juliet 7511 West Here 5589
North Here 2212
Siera 4651
The North Here
of Little Westerly 1235
West Here 5589
Thank you
Sir, Still I am waiting for your positive reply.
Binoy
Dear Sir,
I am trying to find value and display in answer columns but we are not success. please guide me.
see the table.
Sir I hope you reply as early as possible.
Inst_Code course_code Vacant Inst_Code Course_Code Vacant Answer
4 16 1 4 16 1 1
4 16 1 4 16 1 1
4 16 3 4 17 3 #REF!
4 16 2 4 17 2 #REF!
4 17 2 4 23 2 #REF!
4 17 1 4 29 1 #REF!
4 22 1 4 30 1 #N/A
4 22 1 4 22 5 #REF!
4 23 3 4 17 6 #REF!
4 28 2 4 17 2 #REF!
4 28 2 4 30 3 #N/A
4 29 1 4 29 4 #REF!
{=INDEX($A$2:$C$2,MATCH(1,(E2=$A$2:$A$13)*(F2=$B$2:$B$13),0),3)}
Sir, I request you please reply as early as possible.
Thanking you,
Binoy
Hi Svetlana,
How can I use the INDEX and MATCH with a four dimensional look up (3 Vertical and 1 horizontal)? I tried extending the two dimensional example and it did not work.
For Eg:
January February March April
Leather Black Big Bag 100 200 300 400
Leather Red Small Bag 200 300 400 500
Leather Green Big Bag 300 400 500 600
Leather Yellow Small Bag 400 500 600 700
Cotton Black Big Bag 500 600 700 800
Cotton Red Small Bag 600 700 800 900
Cotton Green Big Bag 700 800 900 1000
Cotton Yellow Small Bag 800 900 1000 1100
i want to write a formula to look up the value for the following :
Cotton Green Big Bag January
Cotton Red Small Bag March
Leather Yellow Small Bag February
Request your help please
In the table under #calculations-index-match, you refer to a function that gets the row number of the maximum value as one that gets the row number of the minimum value and vice versa. Also, near the top of the page, you refer to "MATCH(lookup_value, lookup_array, [match_type])" as the syntax of the INDEX function.
I did not realize there were so many "bugs" in this post. I should probably find a good proofreader... Thanks again for pointing out those errors for me.
In the first paragraph below #index-match-basics, where is says "a combination of INDEX and VLOOKUP functions", I believe you meant "a combination of INDEX and MATCH functions".
Hi Hank,
Spot on :) Thanks very much, fixed!
I am trying to create a spreadsheet where it will put a value from a table depending on the code entered and name of insurance provider. Would this be the best tool to use?
Hello,
I am trying write a function to count the number of unique values in one column that fall in a category in another column.
Here is a simplified example of my data:
Order Qty Group
1 1 1
2 2 1
3 1 1
4 4 1
5 5 2
6 8 2
7 2 2
8 4 2
9 6 2
I want to know how many different/unique order quantities there are within group 1, group 2, etc but without designating the cell range, just the particular value (1 or 2 in this case).
Thanks so much!
Q column A contains a series of data from 1 up to 100 numbers, while column B have data 1 upto 150 numbers but two times.compare A and B column then hilight matching ones. Thanks 4 ur comments
Example:
A B A+B Up-to-date
1.00 2.00 3.00 3.00
How can it be possible in the fourth cell to remain the value 3 even after editing A & B Cell Value as well as yield previous sum-total + Current sum-total effect in the same fourth cell.
Say, if I delete the value 1 & 2 from A & B; A+B can be 0 but Up-to-date value will show 3 and again if I put 2 in A & 3 in B; A+B = 5 but Up-to-date value have to be 8
A B A+B Up-to-date
2.00 3.00 5.00 8.00
No manual operation except putting value in A & B Cell rest are linked by formula. Please Help..
Hi,
I am trying to return multiple values using multiple lookup values. I have a range of lookup values, and I need to list the information from another sheet for each instances the any one of those lookup values appears on my data sheet. Suppose I had X,Y,Z for lookups, and I multiple data points for X,Y,Z on a another sheet. I need to create a single list that will show each entry that appears on the second sheet for any of X,Y,or Z. My current formula is:
=IF(ISERROR(INDEX(Data!$A$1:$E$10000,SMALL(IF(Data!$D$1:$D$10000=Formulas!$A$2,ROW(Data!$A$1:$E$10000)),ROW(Data!1:1)),1)),"",INDEX(Data!$A$1:$E$10000,SMALL(IF(Data!$D$1:$D$10000=Formulas!$A$2,ROW(Data!$A$1:$E$10000)),ROW(Data!1:1)),1))
That works fine, except that I have a list of 34 lookups in addition to Formulas!$A$2. Currently, it will only give me all the entries for the first lookup in my list. I can't figure out how to list the entries for the first lookup, then the second, etc.
Thanks for any help you can provide.
found the answer to my second question - it was my fault - had closed parenthesis in the wong place - so while formula was syntactically ok, it returned a wrong result.
Thanks for the article which is a very helpful introduction into using index/match.
One of the most interesting use cases (for me) is (or would be) using this index/match for looking up data in a (MS Excel) TABLE that resides not in the same, but in a different work book.
I have played around with this and achieved some results, but also found that data is not refreshed and/or even not found anymore when the data in the table changes. My first question would be "what is the right syntax to use? and do I need a 2-way index/match for this or not"? So making up a simple example to illustrate:
My workbook with the table holding the data is called 'CustomerFile.xlsx'. The table is located in tab sheet 'customers' and is called 'Table_Customer'. The columns on the table are: 'Customer ID' and 'Customer Name'. The table has two rows:
Customer ID Customer Name
-------------------------
1 John
2 Marc
My workbook into which the result should be pulled is called 'CustomerRpt.xlsx'.
So what formula would I be using to get the name of customer with ID 1 WHEN SPECIFICALLY WANTING TO USE TABLE AND COLUMN NAMES (rather than column references like $A:$A).
=INDEX ('CustomerFile.xlsx'!Table_Customer[Customer Name], Match(1, 'CustomerFile.xlsx'!Table_Customer[Customer ID],0))
OR
=INDEX ('CustomerFile.xlsx'!Table_Customer[#Data], Match(1, 'CustomerFile.xlsx'!Table_Customer[Customer ID],0), Match("Customer Name", 'CustomerFile.xlsx'!Table_Customer[#Headers],0))
My second question would be: "Any idea why sometimes a correct result is only returned when re-typing the complete formula from scratch?" Note: I am re-tying the EXACT same formula. I am aware the workbook with the source data needs to be open and I have tried refresh/refresh all, formula automatic refresh turned on, saving the file and coming back in, editing link and update values, but only retyping the formula brought back the correct result! So is there any caching going on?
Thanks
A B C D E F G H
1 X AXN Ali 11.11 AXX Ahmed 10.19
2 X AXT Hassan 13.19 AX9 Brian 11.11
3 Y AXX Ahmed 10.19 AXP Shah 0.0
4 Y AX9 Brian 11.11 AXN Ali 11.11
5 Y AXP Shah 0.0 AXT Hassan 13.19
Please help:
Compare the value from Column "F" with Column "C" and on perfect match paste the corresponding average from Column "E" to Column "H". For example: AXX (F1) matches with C3 where the corresponding average is 10.19 and auto pastes it to last column i.e. H (H1)
I have been practicing the formulas that (Look Up with Multiple Criteria Using Index/Match and Vlookup. and I have learned other two ways to lookup using index and match:
=INDEX(Table9[[Customer]:[Qty.]],MATCH(1,((Table9[Customer]=B14)*(Table9[Product]=B15)),0),3) >Yours.
=INDEX(Table9[[Customer]:[Qty.]],MATCH(B14&B15,Table9[Customer]&Table9[Product],0),3) _ Index/Match with &
and using nested If function:
=INDEX(Table9[[Customer]:[Qty.]],MATCH(B14,IF(B15=Table9[Product],Table9[Customer]),0),3)
and the vlookup function with &:
=VLOOKUP(B14&" "&B15,Table9,4,FALSE)
these 3 formulas give the same results and I just want to share them.
)))
thank you for your time you put on explaining excel.
my best wishes.
please anybody resolve my problem.
SHEET 1
A B
DATE NO.
01-May-10 R/RES-105255
02-Apr-14 R/RL-813301
21-Jul-10 R/RES-148991
02-Apr-14 R/RL-813301
27-Oct-12 R/RL-557675
21-Jul-10 R/RES-148991
23-Jul-09 R/RES-100983
30-Sep-12 R/RL-614014
27-Mar-15 R/RL-574613
27-Mar-15 R/RL-574563
01-Jan-09 R/NN-53444
04-Mar-15 R/RL-950289
05-Feb-14 R/RES-100983
01-Jan-09 R/NN-70988
30-Sep-12 R/RL-614014
30-Mar-15 R/RL-616791
05-Jun-13 R/RL-610994
11-May-11 R/RL-143811
16-Feb-14 R/RL-824415
01-Jan-09 R/NN-109879
09-Nov-11 R/RES-30872
01-Aug-13 R/RL-707247
19-Jun-13 R/RL-727757
SHEET 2
A
NO.
R/RES-105255
R/RL-813301
R/RES-148991
R/RL-813301
R/RL-557675
R/RES-148991
R/RES-100983
R/RL-614014
R/RL-574613
R/RL-574563
R/NN-53444
R/RL-950289
R/RES-100983
R/NN-70988
R/RL-614014
R/RL-616791
R/RL-610994
R/RL-143811
R/RL-824415
R/NN-109879
R/RES-30872
R/RL-707247
R/RL-727757
I WANT TO GET EARLIEST DATE IN SHEET2 COLUMN B FOR EVERY CELL WITH THE HELP OF SHEET 1
Thanks for the valuable information.
I have 3 columns of data.
Column 1 - Vendor
Column 2 - Items
Column 3 - Price
Data would look like this:
Vendor A Item A Price $1.00
Vendor B Item A Price $1.05
Vendor C Item A Price $1.10
Vendor D Item A Price $1.15
Vendor A Item B Price $0.50
etc
It's a straight data dump from SAP; basically in column 4 (detailed as listed above) would be the formula that I'd like to find the min price for Item A (Data is in reality scattered throughout 1000+ lines and not next to each other.
I was think a index/match/min formula to capture this but I can't figure the formula; any help would be appreciated!
I have four columns of criteria. Some rows have four cells of criteria but some have only three. My formula is: INDEX(Fb,MATCH(1,INDEX((A4=SYSTEM)*(B4=SPECIE)*(C4=SIZE)*(D4=GRADE),0),0)). This works well as long as the row containing the match has criteria in all four cells but it fails when the correct match is in a row containing a blank cell. I need a method which will treat a blank cell as a match.