You are here
Use INDEX to lookup multiple values in a list
Excel's VLOOKUP function is excellent when you want to find a value in a table based on a lookup value. But if your table includes your lookup value multiple times, you'll find that VLOOKUP can't do it. This lesson shows you how to use the INDEX function (plus some other functions) to find all matching values in a list, and return a value from another column in the same row. It also looks at how to do this when you want to return all values which are a partial match (i.e. a wildcard search) to the values in your lookup table.
Here's a simple example based on a question I was asked by one of our readers.
 There are two worksheets, one containing Form data for a mail merge, and one containing Parcel data, some of which is to be included in the mail merge.
 The Form worksheet includes a row for each landowner who is to receive a letter. One of the columns in the spreadsheet is an ID for each landowner.
 The Parcel worksheet contains data about land parcels owned by the landowners in the Form worksheet.
 The mail merge will generate a letter to all land owners. That letter should include details of all of the land parcels owned by that individual land owner.
In this lesson I'm going to focus on how to include that land parcel information in the mail merge, but I'm not going to discuss how to set up the mail merge with Microsoft Word.
Use the Index function to return multiple values a list
Let's say our Parcel worksheet looks like this:
As you can see, John Smith (ID H240) appears three times. We want to include a list of the crops John grows in our mail merge letter. If you've used a VLOOKUP function before, you'll know that we can use it to find the first crop for John in the list, but not the second or third.
Find all the row numbers to match our lookup value
Let's start solving our problem by first finding the row number of each row in which H240 appears, by using the formula below. After that we'll then extend this formula to find the crop value in each row:
{=SMALL(IF($A$2:$A$6=$A$9,ROW($A$2:$A$6)),ROW(1:1))}
Here is a breakdown of this formula:

The { } around the formula indicates that this is an array formula.
 Note that you don't type these braces as part of the formula. You have to press Ctrl+Shift+Enter key when entering the formula to tell Excel it is an array formula, after which the { } will automatically appear. If you don't, our formula will return a #VALUE error.
 If you're using Excel for Mac, you'll need to press CMD+SHIFT+Enter instead.

The SMALL function has the syntax SMALL(array,k).
 It looks up a list and finds the k'th smallest value in the array.
 If k = 1 it will find the smallest. If k=2 it will find the second smallest value, and so on.
 The second instance of the ROW function is used to find k in our formula. In this example, ROW will return the row number of row 1, which is, of course, 1.

The IF function looks to see if the value in A9 is in the list.
 In our example, A9 is where we'll enter H240 as our lookup value.
 Note that the IF function will only return a value if our number is in the list.
 If not, it will return a null value, which will cause the SMALL function to generate an error. We'll look at how to hide that error later.

The ROW function is used twice:
 If the IF function finds our value in the list, the ROW function returns the row number in which it was found. Note that it returns the row number of the worksheet, not the row number inside our table. This will be important later.
 As noted above, the ROW function is also used in our formula to return k.
The screenshot below shows this formula in action:
In the screenshot above, I've entered h240 into A9 (you'll note that our formula isn't case sensitive). I've then entered the formula above into A11 as an array formula (don't forget that part!). It has returned 2, which is the row number for the first instance of H240 in the list. After that, I've copied and pasted the formula into rows A12 to A13. The formula has automatically picked up the correct row numbers, and generated an error when it couldn't find a fourth value.
However, there has been some magic under the hood that you can't see here. When I copied and pasted the formula into the additional rows, the array formula changed. Here are the formulas in each cell:
{=SMALL(IF($A$2:$A$6=$A$9,ROW($A$2:$A$6)),ROW(1:1))}
{=SMALL(IF($A$2:$A$6=$A$9,ROW($A$2:$A$6)),ROW(2:2))}
{=SMALL(IF($A$2:$A$6=$A$9,ROW($A$2:$A$6)),ROW(3:3))}
{=SMALL(IF($A$2:$A$6=$A$9,ROW($A$2:$A$6)),ROW(4:4))}
Note how the ROW parameters in each formula are different: 1:1, 2:2, 3:3, 4:4. This is telling the SMALL function to find the 1st, 2nd, 3rd and 4th values in the list that match our lookup value. As I said, you don't need to edit the formula yourself  it updates itself when you copy and paste it.
Use the INDEX function to find the Crop values
Now that we know the row numbers for each instance of H240 in our example, we can use the INDEX function to use the row number in order to obtain the Crop value. If you're not familiar with the INDEX function, you can read our lesson on INDEX here (it will open in a new tab).
Here's the adjusted formula:
{=INDEX($A$2:$C$7,SMALL(IF($A$2:$A$7=$A$9,ROW($A$2:$A$7)),ROW(1:1))1,3)}
Here's how this function breaks down:
 The INDEX function looks in our table ($A$2:$B$7)

It then uses the SMALL function to find which row to look in and then subtracts 1 from what SMALL tells us.
 Remember that the ROW function returns the row in the worksheet where our value was found.
 The INDEX function considers the first row of our table as row 1. Because our table data starts in row 2, we need to subtract 1 from the ROW value to get the correct row inside the table.
 If you're applying this solution to your own spreadsheet, you may need to adjust this value to take account of where your data table is located.
 The INDEX function then looks in column 3, which is where the crop data is found.
Here's how our spreadsheet looks now.
Hide errors generated by our formula
At this point, we've done what we set out to do, which was find all of the crops related to John Smith. However, if we are going to use this solution in a mail merge, we don't want the mail merge to print #NUM in our letters. Let's finish off this lesson by looking at how you can hide error values when they occur in our formula:
{=IF(ISERROR(INDEX($A$2:$C$7,SMALL(IF($A$2:$A$7=$A$9,ROW($A$2:$A$7)),ROW(1:1))1,3)),"",INDEX($A$2:$C$7,SMALL(IF($A$2:$A$7=$A$9,ROW($A$2:$A$7)),ROW(1:1))1,3))}
This formula looks terrifying, but it's actually very simple.
 The IF function tests to see if our formula generates an error. It does this by using the ISERROR function.
 If the ISERROR function is true (i.e. our formula generates an error) then the IF function returns "", which means the cell containing our formula will appear to be blank.
 If the ISRROR function is not true, then our formula works OK, and the IF function will use it to return the correct value.
There's not much to see when we use this version of the formula, but here is what our spreadsheet looks like now:
Finding all matching values using partial match
A couple of people have asked in the comments below whether there was a way to use a wildcard, or partial match, for the lookup value in A9. The answer is yes  and here's the formula to use  you can replace the array formula in A11:A14 in the example above:
=IF(ISERROR(SMALL(IF(IF(ISERROR(SEARCH($A$9,$A$1:$A$7)),FALSE,TRUE),ROW($A$1:$A$7)),ROW($C$1:$C$7))),"",INDEX($A$1:$C$7,SMALL(IF(IF(ISERROR(SEARCH($A$9,$A$1:$A$7)),FALSE,TRUE),ROW($A$1:$A$7)),ROW($C$1:$C$7)),3))
This formula uses the SEARCH function to determine if the lookup value in row 9 can be found in the cells in A10. In practical terms, this formula allows you to type a partial match, e.g. H24, and the formula will return all instances where H24 is found in column A. Note that you don't need to use asterisks, *, to indicate to Excel that you are using a wildcard  you simply type the partial string you want to match.
Note that there is a twist in this final example. You can't copy and paste the formula from A11 into A12  A14 as you could in earlier examples. You have to do the following:
 Get the array formula working in A11, and returning the correct result (Beans in my example).
 Select all the cells where you want this formula to be (including the cell with the first working example). In my example it is A11:A14. Make sure the active cell is the one containing the formula.
 Click in the formula bar to start editing the formula but don't change anything.
 Press the keys to enter an array formula (CTRL+Shift+ENTER on PC or Command+Shift+Enter on Mac).
 The selected cells will change to show you the correct results, and the selected cells will become an array.
Once you do this, the selected cells can only be treated together as an array. If you try to edit, say, A13, you'll get an alert telling you that you can't edit an array. If you want to change your formula, you need to select all of the cells in the array, click in the formula bar, and then press the keys to reenter the formula as an array formula. If you simply want to break the array, select all of the cells, click in the formula bar and press Enter.
Summary
And with that, we're finished. If you have any questions about this solution, please let us know in the comments!
* This lesson was inspired by a post by Ashish Mathur for Excel 2003. You can read that post here. The inspiration for this lesson came from a question from one of our readers on the original lesson on how to use the INDEX function. The formula for using a wildcard came from this forum discussion. All links open in a new tab.
Our Comment Policy.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat  we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note  if you post a link in your comment, it will automatically be deleted.
Comments on this lesson
VLOOKUP should work for this scenario
Hi Michael
I recreated your spreadsheet and verified that VLOOKUP will work in the scenario you've described. I don't see anything in that would require you use the INDEX function in the way it's used in this lesson.
The only potential problem I can see is if you want someone to type a year, e.g. 2020, rather than the range shown in your table above, either 2019/20 or 2020/21. In that case, this scenario won't work because VLOOKUP is looking for a match between what is typed in C14 and the values in the first column of your table. A value like 2020/21 is treated as a text value by Excel, so the text you type into C14 must match that text value.
If you want someone to be able to type 2012 and have the formula return the correct result, you'll need to respecify the data table so the first column matches the potential values the users might type, i.e. 2013, 2014, 2015 etc.
If you're unable/unwilling to respecify the values in the table, you could respecify the lookup value. The following formula shows a calculation that will take a year, e.g. 2013, and convert it into a text value (2013/14) so that it can be used in your VLOOKUP function.
=C12&"/"&C121999
What it does is take the value in C12 and creates a text value by concatenating additional characters, starting with a / symbol, followed by another number. That number is the value in C12 (e.g. 2013) less 1999 (which gives us 14). So the result would be 2013/14, which is a text value. If you use this as the lookup value in VLOOKUP, it should return the result you want.
I hope this helps!
Regards
David
Great Infomation !
Thank you for giving me the opportunity to visit this impressive website. There is plenty of valuable info listed on this site.
I have seen a lot of tricks. It is too much to absorb at once, thus I definitely will return to view more
http://www.jumbojobs.com/seojobs
Expanding the INDEX function
Hi,
Thank you ever so much for the above  very helpful!
I was just wondering if you might be able to help me expand the formula somewhat? I am interested in combining this with another IF and checking against an additional column e.g. if column A = M AND column B = Y then use INDEX to find the different values e.g. the first instance of Y=Z, the second instance of Y=Z, the first instance of Y=T, the second instance of Y=T, the third instance of Y=T etcâ€¦
For reference, within this worksheet: Column A contains my references (the Ms mentioned above) and row 3 contains the numbers 1, 2, 3 etc. for the ROW(1:1) part e.g. first, second, third instance (I am dragging my formula horizontally rather than vertically as in your example above). Row 2 contains the Y criteria.
In the other worksheet (containing the data I want to search through): Column_A is the column containing the Column A values from the previous sheet (the Ms) and Column_B contains the second search criteria. Search_Area is the full table. Header_Row is the header row and â€œX" and â€œY" are some of the column labels.
My original formula is:
=IF(ISERROR(INDEX(Search_Area,SMALL(IF(ISNUMBER(SEARCH($A6, Column_A)),ROW(Column_A)),ROW(INDIRECT(B$3&":"&B$3))),MATCH("X", Header_Row,0))),"",INDEX(Search_Area,SMALL(IF(ISNUMBER(SEARCH($A6,Column_A)),ROW(Column_A)),ROW(INDIRECT(B$3&":"&B$3))),MATCH("X",Header_Row,0)))
Iâ€™ve expanded it to:
=IF(ISERROR(INDEX(Search_Area,SMALL(IF(ISNUMBER(SEARCH($A6,Column_A)),ROW(Column_A)),ROW(INDIRECT(P$3&":"&P$3))),MATCH("X",Header_Row,0))),"",IF(INDEX(Search_Area,SMALL(IF(ISNUMBER(SEARCH($A6,Column_A)),ROW(Column_A)),ROW(INDIRECT(P$3&":"&P$3))),MATCH("Y",Header_Row,0))=P$2,INDEX(Search_Area,SMALL(IF(ISNUMBER(SEARCH($A6,Column_A)),ROW(Column_A)),ROW(INDIRECT(P$3&":"&P$3))),MATCH("X",Header_Row,0)),""))
But it only works if the first value is Y=P2 which it isnâ€™t always! Is there a way to achieve this without splitting out all of the data into separate worksheets?
Many thanks!
Any joy?
can i use this lesson with a "Column" instead of "ROW"?
hello
i have the following row that includes 9 names (per working day). i can't know the names in advance, and there might be the same guy appearing twice.
below that row i have another one with the kilometers each guy drove.
for example:
Nick  Henk  Sidney  Nick  Roberth  Mark (so as you can see Nick is there twice, becasue he worked 2 days in that week).
100  210  150  190  45  76
i total, i will have 52 rows with names and 52 rows with KM, to cover the whole year...
i want to have a side table to show me how mny KM each guy did on every week.
so in the above example i'd like to have this:
week 1  Nick  Henk  Sidney  Nick  Roberth  Mark
KM  290  210  150  190  45  76
so as you can see, Nick's result was 290 (100+190) becasue he worked twice.
i thought your trick with the INDEX and ROW was almost what i need, but i think i have to find the occurences of "NICK" looking DOWN, right?..
please help!... :(
thanks!
Ran
A simple SUMIF function should do what you need
Hi Ran
It looks like you can do what you need with a simple SUMIF. People tend to think of SUMIF as being used for adding up data in columns, but it works just as well to add up data in rows provided you set it up correctly.
Let's work with this example from the spreadsheet you provided.
Here's the source data:
Here's where you want to calculate the results. The table starts in M13 with Amir's name (it's a little confusing, but the 18 in this picture refers to the Round from the picture above, not the row number in Excel):
The formula in round 18 for Amir is this:
=SUMIF($C2:$K2,M$13,$C4:$K4)
This formula looks in the cells from C2:K2 (the names in the table) and matches them with the value in M13 (Amir). If there is a match, it adds in the calue from the appropriate cell in the range C4:K4 (kilometers driven by that person).
I've written this formula using a mixture of absolute and relative references so that you can copy and paste it from Amir to Yaron without changing it. However, the layout of your spreadsheet means that you can't just copy the same formula down to Round 19 without adjustment.
Here's how the formula in Round 19 would need to look:
=SUMIF($C5:$K5,M$13,$C7:$K7)
As you can see, I've had to adjust the range references (e.g. $C2:$K2 became $C5:$K5), but not the reference to Amir's name (M$13).
I hope this helps. Let me know if you still have any questions.
You can read more about the SUMIFS function in this lesson.
Regards
David
THANK YOU!
indeed, you are right! i guess i was complicating things unnecessarily...
talking about this same spreadsheet, i have another question if you don't mind, in related to using of "IF"...
in the attached file, i added another list. i want to see which operator worked at the following stadiums (Groningen, Roda JC, Cambuur).
so i used this formula to search within the data of round 18:
=IFERROR(INDEX(C2:K2,MATCH($N$2,C3:K3,0)),"")
so N2 = Groningen in that case.
now, if i want to auto copy this to all the way to the bottom, to cover rounds 19,20,21, etc etc, i can't use the drag method (from the corner of the cell), because it will increment the C2:K2 to be C3:K3. while i actually need it to be C5:K5... (this is where the data of round 19 is).
so me question is:
1 how can i tell Excel to autoincrement by 3 numbers? so to jump from C2 to C5 to C8 etc etc?
2 if the above question is not possible,how can i add another "MATCH" parameter to also check the round number?
for example:
Match N2 (Groningen), but also match the round number (for example M6, M7 etc etc)?...
i hope i explained my self correctly...
many thanks for helping so far!
Ran
HELP!
Basically, I'm creating a group registration form wherein each person can register for multiple events (77 choices). I will be leaving several columns for them to fill the events they want to register for. So each form will have multiple names, each name having several items attached to it.
Postregistration, I would need to be able to pull out the list of people who registered for each event. Can I alter this formula to work for me? The main issue is people can register for the same events but end up typing it in the a different column since I'm leaving several open. If so, how? I attached a sample sheet. Appreciate any help I could get on this matter.
Almost there!
Hello! I have your website to be of the utmost help. I appreciate the time you put into it, so thank you for that. I am writing as I'm pulling my hair out trying to figure out my work.
I have two tables, one on sheet1 and the other on sheet2. They are setup in identical position, and their headers are identical. What I am looking to do is:
1. find where in the first table a variable occurs (say variable a1).
2. Count the instances of each unique variable. (I've accomplished this with copy and paste from pivot tables)
3. if variable a1 occurs three different instances in the first table, I would like to extract the corresponding data from table 2, that is in the exact same row/column as variable a1  table2 holds an index of sorts.
here would be an example (aside from the file I uploaded):
table 1
header1 header2 header3
row1 a1 a2 a3
row2 a2 b1 a1
table2
header1 header2 header3
row1 1 2 3
row2 4 5 6
My goal is to output this:
variable occurance index1 index2
a1 2 1 6
a2 1 2...
any input would be greatly appreciated! I tried to capture the row and column of each variable, however the COLUMN function isn't behaving the same as the ROW function. I keep getting returned the COLUMN for the first occurance of the variable.
What happens if the value of the cell contains a blank?
Hi  this lesson really helped me and I was able to modify it for what I need, but I have one question  if one of the cells that the formula picks up, is blank but I still want it to show on my new worksheet as a blank in that cell, how do I write this into the formula?
Taking your example  looking at row 5  A5 ID is H240, B5 is John Smith but C5 was blank, how do I get it to show as a blank cell  it currently returns a "0" or if formatted as a date field "00/01/1900". If you can help  this formula would be perfect for what I need! Many many thanks!
Add an extra IF function to test for zero
Hi Nikki
What you need is actually pretty simple  simply add another IF function in to check if the result is 0, and output a blank cell if it is. Here's the formula to use in the example above:
{=IF(ISERROR(INDEX($A$2:$C$7,SMALL(IF($A$2:$A$7=$A$9,ROW($A$2:$A$7)),ROW(1:1))1,3)),"",IF(INDEX($A$2:$C$7,SMALL(IF($A$2:$A$7=$A$9,ROW($A$2:$A$7)),ROW(1:1))1,3)=0,"",INDEX($A$2:$C$7,SMALL(IF($A$2:$A$7=$A$9,ROW($A$2:$A$7)),ROW(1:1))1,3)))}
All I've done is put another IF function into the formula (highlighed in bold). So the formula does this:
 Check if the INDEX function returns an error. If so, return "" (a blank cell)
 If it doesn't return an error, check if the INDEX function returns a zero. If so, return "" (a blank cell)
 If it doesn't return a zero, return the value from the INDEX function.
I hope that gives you what you need!
Regards
David
(p.s. I also fixed an error in the final part of the original lesson where I added error checking)
skip instead of blanking
Hi, David, Is there a way to make it skip this blank cells or the zero values themselves? this works almost as I need it to work, the only problem is, I just want the data, I made an offset chart with paired with this formula, the problem is, the chart shows all this extra series that cointain purely zeroes, this is no problem, the chart shows the actual info I need, but for instance, you need to hover over the chart lines in order to know exactly the data is showing, and if I could filter this zeroes without using filters of course, that would be great.
returning multiple answers
I've read your comments on using 1 item to bring in multiple corresponding answers, but can this same approach be used for multiple items to bring in multiple answers? On the example attached, the first column is the item name. The 2nd column I've concatenated the quantity arriving with the expected arrival date. Is there a why to use a vlookup on a huge number of items not just one? I have thousands of items repeating with different quantities arriving on different ship dates. thanks.
Mistake in the INDEX formula?
Hello all,
As it seems vlookpu helps me only so far so I started looking for a solution to similiar problem my company has and the solution seems to be the formula mentioned here "Use the INDEX function to find the Crop values" of course adjusted to the values of my company. As I am quite new to the array formulas I started from the scrap a tried to recreate the formula mentioned here:
{=INDEX($A$2:$C$7,SMALL(IF($A$2:$A$7=$A$9,ROW($A$2:$A$7)),ROW(1:1))1,3)}
for some reason it will not work as the attached example shows. Please be so kind see the file and let me know what I am doing wrong.
It might as well be that this formula does not help my original problem either so here is what I am looking for:
Company name Contract number Employee assigned to work the contract
Company1 Contract001 Employee 1
Company1 Contract002 Employee 1
Company 2 Contract006 Employee 1
Company 3 Contract009 Employee 2
Company 4 Contract 010 Employee 3
What I need to have is that if I put the company name into a cell, that it shows all the contracts this company has opened with us and that it also shows the person that worked this contract in the past and automatically assigns the employee to it or viceversa, if I put the contract number that it always shows the company name related to it and the employee who worked the contract/customer in the past.
Vlookup would work for me only if there always was "one company with one contract" kind of scenario. The problem is that most of the companies have more contracts opened with us and vlookup shows only one contract but is able to assign the right person to it.
I hope this description is enough to understand my demands on the formula and I am convinced that the formula I am trying to recreate in the attached sheet will do once I see where I did the mistake.
Thank you in advance
Regards
Ondrej from the Czech Republic
Mistake in the INDEX formula? attached file
Please see the file
Thank you very much for the
Thank you very much for the article, it worked very well.
I was wondering if there is a modification to pull the list horizontally instead of vertically.
What if I do not want to copy paste formula
What if I do not want to copy paste the formula and just want all the values corresponding to H240 in the column. This is because I might have a column with 1000 rows and I want all the values corresponding to a lookup value in a particular column. Any way this can be done?
Multiple lookup values
Great article! Very clear and easy to follow.
I have a question on whether a variation of this solution is possible to solve my requirement. Your solution works when there is 1 lookup value.
Let's call this lookup value, "ABC123". So, right now I have a worksheet populated with all the data I have configured to be displayed based on the lookup value, "ABC123", present in each row. However, when the lookup value changes (to say, "DEF456") no more data is extracted. I am aware this occurs because of the ROW(1:1) function. Once the lookup value changes the ROW(1:1) function.
Let's look at this with some dummy data so it is clearer. Say I have 5 rows with the lookup value, "ABC123", and another 5 rows with the lookup value, "DEF456". The ROW function will be ROW(5:5) when we come to the fifth row and other indexed data will be displayed in rows 1 to 5. On row 6 the lookup value changes to, "DEF456". The ROW function will be ROW(6:6) and no indexed data will be displayed in rows 6 to 10.
My question is whether there is a formula that will pull all the data and consolidate them regardless what the lookup value might be. Right now, if I want this information I will have to apply the formula separately to the indexed table whenever the lookup value changes.
Any advice you could provide would be much appreciated.
Cheers!
Four years late, but still
I am solving this problem now, I think you could use if function for the row(1:1) problem, so it would look something like this: if(Cell1=Cell2,row(1:1),row($1:$1)). Hope it gives you some joy. Maybe you're still trying to solve it, who knows?
Looking for multyiple occurances of strings withion cells
Hi
I've looked at many web sites looking for an answer to this. I have tried this solution but it is not working. You explain formulas with a good level of detail, thank you, so hence I have a question. I have workbook1 with a sheet (sheet1) containing 6000 rows, 1 column has a product description and a 2nd column has a code number for each description. I also have 6 further work sheets (sheetOTH) that contain a column with a short description. Sheet1 is the client and sheetOTH are suppliers. The descriptions in each sheet are not the same though the descriptions in sheetOTH do appear in part of the description of sheet1. My challenge is to find all the occurrences of the short descriptions from sheetOTH that appear in sheet1 and return the codes from sheet1 to sheetOTH. The aim is to look at each occurrence and match lines in sheet1 with corresponding lines in sheetOTH.
I thank you in anticipation.
1 addition: remove repeated crops
Great example! However, I need to remove repeated occurances of the same return. For instance, if "beans" shows up 5 times, I only want to list it once. So:
Beans
Beans
Beans
Corn
Maize
Beet
Corn
Would return a list of "Beans, Corn, Maize, Beet".
Any idea?
Remove Duplicate Return values
Great example. However, I was wondering if there is a way to remove duplicate items from being returned. For instance, if "beans" occures more than 1 time in the list, can you return the value only one time?
So if the list is:
beans
corn
maize
beans
beans
beets
corn
the list of values returned are "beans", "corn", "maize", "beets" without repeated entries?
PS: apologies if this is a duplicate post, the earlier post seems to have disappeared. (???)
sumif returns 0 though it is not 0
I have a problem in excel.my excel workbook has a sheet (1), which have number in j5. In k5 there long if formula , which calculate data from j5 , =IF(J5<1,"0",IF(J5<11,"2",IF(J5<21,"4",IF(J5<31,"6",IF(J5<41,"8",IF(J5<51,"10",IF(J5<61,"12",IF(J5<71,"14",IF(J5<81,"16",IF(J5<91,"18",IF(J5<101,"20",IF(J5<111,"22",IF(J5<121,"24",IF(J5<131,"26",IF(J5<141,"28",IF(J5<151,"30",IF(J5<161,"32",IF(J5<171,"34",IF(J5<181,"36",IF(J5<191,"38",IF(J5<201,"40",IF(J5<211,"42",IF(J5<221,"44",IF(J5<231,"46",IF(J5<241,"48",IF(J5<251,"50",IF(J5<261,"52",IF(J5<271,"54",IF(J5<281,"56",IF(J5<291,"58",IF(J5<301,"60",IF(J5<311,"62",IF(J5<321,"64",IF(J5<331,"66",IF(J5<341,"68",IF(J5<351,"70",IF(J5<361,"72",IF(J5<371,"74",IF(J5<381,"76",IF(J5<391,"78",IF(J5<401,"80",IF(J5<411,"82",IF(J5<421,"84",IF(J5<431,"86",IF(J5<441,"88",IF(J5<451,"90",IF(J5<461,"92",IF(J5<471,"94",IF(J5<481,"96",IF(J5<491,"98",IF(J5<501,"100",IF(J5<511,"102",IF(J5<521,"104",IF(J5<531,"106",IF(J5<541,"108",IF(J5<551,"110",IF(J5<561,"112",IF(J5<571,"114",IF(J5<581,"116",IF(J5<591,"118",IF(J5<601,"120",IF(J5<611,"122",IF(J5<621,"124",IF(J5<631,"126",IF(J5<641,"128","T"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))).
In another sheet named April in n5 cell i have =IF('(1)'!$A$5:$A$20905=APRIL!$A5,(SUMIF('(1)'!$I$5:$I$20905,APRIL!$M$1,'(1)'!$K$5:$K$20905)),0) formula. This always returns 0, whether k5 has anything. Can you help me?
Better alternatives to Nested IF
Hi DOCKHEM,
There are better alternatives to your nested if in your worksheet. Try below
http://crispexcel.com/6alternativestonestediffunction/
Formula not working sporadically
HI!
I had been searching for a way to do this for quite some time, but it wasn't until I found your explanation that I could get it to properly work. You are doing an amazing job spreading knowledge, thank you so much.
Based on your example, I reconstructed the formula to work with my case, in which I am renaming large batches of pictures. It worked perfectly well until the second time around using it. I'm almost losing sleep over this :).
This is the situation:
I have a bunch of shortened codes ("short EUcode"), each of which links up with one or more long codes ("long EU code). As you can see in the attached example, the tab "TO2" contains two lists, shortened codes in column A and longer ones in column B. In the first sheet, I have constructed your formula to pull the long codes from the "T02" sheet and place them in the same row as the corresponding short code.
This worked perfectly fine the first time doing this.
Now, however, with a new dataset, I run into a problem where the formula does not seem to find anything, while a simple vlookup finds the result. For example the second entry in sheet "Formulas", column A, we find value "2221A.jpg". The formulas in the adjacent cells look for the corresponding long codes in the second tab "T02". No result is given. However, when you do a vlookup for 2221A.jpg in sheet "T02", column A, you find it without any problems, with the corresponding long codes next to it.
I have checked everything I could think of; mistakes in the formula, formatting of the cell, accidental spaces,... No dice. It is really a mystery to me why it worked without any hassle the first time around, but no it acts so strangely.
Any help is greatly appreciated.
Thank again for your wonderful work.
Kind regards,
Koen
Your problem is that the formula isn't working at all
Hi Koen
I haven't found a solution at this point, but I noticed that your formula is never generating the correct outcomes. Specifically, the formula skips the first result in all cases. So an item that has three matches only returns two, and an item that only has one match returns none.....
I can't see an obvious problem with your formula, but hopefully that gives you something more specific to focus on when troubleshooting.
Regards
David
Multiple Results
Hi,
Can you please help me to have a formula in the attached file for multiple results of customer brand. So that in Statement of Account will show all the invoices (vlookup for customer brand).
Thanks,.
Pull based on a common sub string of data.
Instead of linking to a cell ($A$9) I want to pull records based off a sub string (ML) from a larger string that varies (EXT ML, INT ML,,,). I have tried using "*ML*" and that does not work.
my problem is there have
my problem is there have three cells but same number and next column cell different names I need to get the next sheet when I am entering number to get all names for the particular number
eg
25sajeer
25Sufin
25jiju
pls note that I need to use formula in excel with index and match function pls help me
Putting the array table in a different position
Hello,
Thanks for the great explanation. Unfortunately, I can't make it work the way I need it to work.
If I put the array table in a different position eg. H11:I22, I can't make it work.
I guess there is some limitation or complication on how the formula sees the rows. Can't for the life of me work out what it is though.
Here is the file if you want to take a look.
Cheers
J
Can you explain what you're trying to do
Hi James
Looking at the spreadsheet you've supplied, I'm unsure what your formula in E36 is trying to calculate. From what I can tell, you simply want to count the number of rows in H9:H31 that match the value in E34. If that's correct, you could use the COUNTIF function instead.
If that's not what you're trying to do, could you explain your goal so I can figure out what the problem in the spreadsheet is.
Regards
David
Automated checklist
Thanks for the reply David.
I'm trying to make an automated checklist for our training organising.
So the user will select what type of course we are doing in E34 using the list.
A list should then appear below it E36 downwards with all materials needed for that specific training.
I've put the list of items and what course they are needed for in array H9:I21.
So I want E36 to look at E34 and know that I want for ex, ReclamaÃ§Ãµes.
It will then look at H9:I29 and see the list of things that is required for ReclamaÃ§Ãµes.
Hope that's clearer.
Not exact match  cell only containing what is searched for
Thank you for your help!
I now have an addon problem which I cannot solve, which should just be a smaller adjustment. B4:Bx are filled in with the value in Jcolumn of each "La" match in Kcolumn. What I would like to add, is that it works even if the Kcell is for example "La and Ka", because the cell contains "La" even if not an exact match.
Would really appreciate your help! Have tried the search function without success :).
Best Regards Pete, Sweden
=IF(COUNTIF($K$2:$K$17;$B$2)>=ROWS($B$4:B4);INDEX($J$2:$J$17;SMALL(IF($K$2:$K$17=$B$2;ROW($K$2:$K$17)ROW($K$2)+1);ROWS($B$4:B4)));"")
Not sure what the problem is....
Hi Pete
I looked at the spreadsheet you uploaded. Can you give me more information about the problem?
As I understand it, you want to put a value in B2, and then have cells B4 through B10 show all of the values from J where the values in K match the value you've typed in B2.
From what I can see, this is exactly what the formula is doing.
Can you give me some examples of what I should type in B2 to recreate your problem?
Regards
David
... Problem clarification
Big thank you for your quick response, David!
Not sure if I managed to upload the file in the previous post? However, the problem is that it fails to "match" B2 with values in K if it is not an exakt match. If a cell in K contains the value, even if its not the full string, I want it to work the same way as if it was the exakt match.
Eg.: If B2 is "La" and a cell in K is "La" it will now match. If B2 is "La" and a cell is "La and some more text" it does not match now. I want it to match also when "La" is only a part of a cell. :) Am I making it clearer?
Best Regards,
Pete
Getting Multiple values of a match when there are blank rows?
Hi, I have a slightly different and complicated problem. The lookup values I want to search have a blank row in between as in A:A of the file. Also There are blank columns, i.e. the results I want to get have blank columns. How do I solve this. The formula I entered in sheet 2 is not giving desired result. Any help is deeply appreciated.
Use this with wildcards
Is there a way to use this with wildcards to find a number that matches a pattern?
For instance, I need to look up license numbers that have a specific dash pattern.
* Asterisk being the wildcard....
******************
Index / match
I have a case sheet with a household number. I have a household sheet with each member of the family listed on seperate lines, which includes whether the member is registered disabled, but with the same household no. How do I count how many households have a disabled member for each case?
I cannot figure a way to add a function to my spreadsheet  essentially what I want to happen is that when a person enters a year in cell c12, the value of cell c14 changes to the dollar amount of the package for that year  which I have in a table on the same sheet. I tried to get this to work with vlookup but have had no joy. I want to set the sheet up to automatically calculate a range of things with minimal input from users (and decreased errors as a result). If they know the year then I would rather have the sheet automatically populate with the approprate dollar value (which my team updates regularly based on indexation and cost changes).
Any advice would be much appreciated!
Cheers
Michael
Year Average cost
2013/14 $34,969
2014/15 $36,354
2015/16 $37,793
2016/17 $39,290
2017/18 $40,846
2018/19 $42,464
2019/20 $44,145
2020/21 $45,894
2021/22 $47,711
2022/23 $49,601
2023/24 $51,565