In a list with multiple occurrences, the VLOOKUP() function in Excel will only return the first occurrence of the string being searched.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table)

You may watch a short video of my solution here

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system’s performance.

I  have presented three cases in the this workbook.

You may refer to my explanation of the array formula here.

• ordeayn says:

Say the list comprised three different Ashish persons, e.g different family names, but the searcher searched only on the string ‘Ashish’, how would the formula return all instances of Ashish. My example would be say, Ashish Pongal, Ashish Ashish, and Ashish Sanjay. If you search on Ashish, get back all three occurrences.

So in this instance, if you don’t mind using an extra column, use concatenate function to add first and last name, and in the vlookup formula, use concatenate to find the exact match.

something like: vlookup(concatenate(first name column, second name column), \$Column1:\$column2,(enter the concatenated column number in the table), false)

• Ordeayn says:

In the example files that you give, could you set the search routine so that if the name cells contained both forename and city to distinguish each different ‘Ashish’ that searching by forename alone, Ashish, the routine will return all four instances of Ashish with the full cell content: say you had Ashish Bangalore, Ashish Delhi, Ashish Chennai, Ashish Bangalore could you set the routine to find all occurrences of Ashish and return the full entry just searching on ‘Ashish’?

• Hi,

Try this

1. Assume your data is in range B4:B8
2. Enter your search string in cell B12
3. In cell C12, enter the following array formula (Ctrl+Shift+Enter) and copy down

=IFERROR(INDEX(\$B\$4:\$B\$8,SMALL(IF(ISNUMBER(SEARCH(\$B\$12,\$B\$4:\$B\$8)),ROW(\$B\$4:\$B\$8)-ROW(\$B\$3)),ROW(1:1)),1),"")

Hope this helps.

• saviour thomas says:

Dear Sir,
i am using the following formula to get the corresponding detail of a certain cell, but in excel while generating the data is take lot of time “Calculating processor %%%”, is their any alternative like VBA etc to generate the data, please help
=IFERROR(INDEX(updating..!\$A\$2:\$K\$31311,SMALL(IF(\$C\$6=updating..!\$A\$2:\$A\$31311,ROW(updating..!\$A\$2:\$A\$31311)-MIN(ROW(updating..!\$A\$2:\$A\$31311))+1,””),ROW(updating..!K1)),COLUMN(updating..!K1)),” “)

Regards,
Saviour

• Hi,

• anjali says:

Hi Ashish,

Is this formula limited to a small set of data as i tried putting it for big data and it didnt give any result, however it did give perfect results for a small set of data.

• Hi,

The formula should work irrespective of the number of rows. Ensure that the ranges specified in the formula are correct. Also, since it is an array formula, you have to confirm the formula with ctrl+Shift+Enter.

• Sony says:

Hi Ashish,
What if I wanted to lookup and return the multiple occurrences separated by commas in just one cell? What would the formula look like? In my case I search for text, not numbers.
Do you know if that also could be achieved using DAX formulas in POWERPIVOT?
Thanks.
Sony

• Hi,

Yes, this can be done in Excel 2016. In this version, if you have an Office365 subscription, you will get access to a few new Excel functions such as TEXTJOIN() and CONCAT(). These functions will allow you to accomplish your objective. This is also possible in PowerPivot available with Excel 2016.

• ordeayn says:

Thank you. THis solved the problem completely. It would be great if you could do another paper to explain the logic behind this formula. I am simply using it without understanding what each part does, particularly the -(ROW\$B\$3) part.

• You are welcome. The -(ROW\$B\$3) part is simply substracting the first row of data from ROW(\$B\$4:\$B\$8) thus creating a virtual array of consective positive numbes from 1 to 7.

• Suresh R says:

Hi,

Here you have mentioned source with same name but I’m having different sources in which some text string is common. So based on that I need to get some data.

I’ll state my question here & the attachment is in Mera windows. Kindly fetch that file from there…

Question…
* Cloumn A contains some Messages received by a system.
* In Column B I need those messages starts with “ETS FLD”.
* In Column C I need those messages starts with “ETS DGR”.
* In Column D I need those messages starts with “ETS GSM”.
* In Column E I need those messages starts with “ETS CDMA”.
* In Column F I need rest other messages.

• Hi,

A similar question has been asked by Ordeayn (the first comment of this post). Please try out that solution.

• Suresh R says:

Hi,

Thks for your reply. But I couldn’t get the result. I tried with the below mentioned formulae but still shows blank cell when I give a Ctrl+Enter…

=IFERROR(INDEX(\$A\$2:\$A\$706,SMALL(IF(ISNUMBER(SEARCH("ETS FLD",\$A2)),ROW(\$A\$2:\$A\$708)-ROW(\$A\$1)),ROW(\$A\$1)),1),"")

Request you to work on my data & give a result.

• Hi,

Try this array formula (Ctrl+Shift+Enter)

=IFERROR(INDEX(\$A\$2:\$A\$706,SMALL(IF(ISNUMBER(SEARCH("ETS FLD",\$A\$2:\$A\$706)),ROW(\$A\$2:\$A\$706)-ROW(\$A\$1)),ROW(1:1)),1),"")

• Bhagyesh says:

Hi,

I have used the lookup formula to get multiple values for duplicate search strings in my cricket stats spreadsheet. The question I have now is how can I modify this formula {=INDEX(\$B\$2:\$C\$113,SMALL(IF(\$B\$2:\$B\$113=\$N\$2,ROW(\$B\$2:\$B\$113)-ROW(\$C\$1)),ROW(B1:C1)),2)} so that it returns the highest value?

Thanks
Bhagyesh

• Hi,

One easy way would be to use the MAX function on the values which you get from the formula you have used. However, if you wish to get the maximum value via one formula, then try this array formula (Ctrl+Shift+Enter)

=MAX(IF((\$B\$2:\$B\$113=\$N\$2),\$C\$2:\$C\$113))

Hope this helps.

• Bhagyesh says:

Hi,

Thanks for the reply. I tried the formula you gave, but it doesn’t work, just returns 0.

Thanks
Bhagyesh

• Hi,

I tried it on my sample data and it worked just fine. Check for the following:

1. The spelling in cell N2 should match the spelling with the entries in range B2:B113; and
2. The values in range C2:C113 should be numbers (not numbers stored as text). To verify this, in cell D2, enter =ISNUMBER(C2) and copy till cell D113. If all entries are numbers in range C2:C113, values in range D2:D113 should be TRUE.

• Tani Sylvester says:

Hello,
I am using your original formula (=INDEX(A21:F23,SMALL(IF(A22:A23=A28,ROW(A22:A23)), ROW(1:1)), 2) and trying to return multiple values for all items listed as “Open” on multiple tabs. I’m starting with the first tab of data (labelled Sports) and the formula keeps producing a Value error. Can you take a look at the data and see where I’m going wrong?

• Tani Sylvester says:

Hello,
The formula is working beautifully now – I played around with it a little and it’s great. I do have an issue, however… I have multiple tabs within my worksheet. On my current tab, I’m trying to consolidate all of the “Open” items displayed on the various sheets. If my current tab indicates a particular brand ie Style and I only want the formula to search for Open items within the Style tab, how can I do this? I’m thinking there must be a nested IF function within your formula. If I have tabs labelled Style, Tech, Sports, how do I add to your formula so that only data in the relevant tabs are searched for?

• Hi,

Assume the following setup

1. Let’s assume that the tab where you are consolidating all information is called All Brands. On this tab, one may select any brand from a drop down list in column A. The brand selected here should be the same as one of your worksheet names. Headings are in row 1
2. In column B of the All Brands sheet, one may select Open or Closed from a drop down list
3. On all other tabs, Open and Closed are available in column C and Item descriptions are in column A. Headings are in row 1
4. Spelling and order of headings on all sheets (including All Brands sheet) are the same
5. In cell C2 of All Brands sheet, try this array formula (Ctrl+Shift+Enter)

=INDEX(INDIRECT("’"&A2&"’!\$A\$2:\$F\$416"),SMALL(IF(INDIRECT("’"&A2&"’!\$C\$2:\$C\$416")=\$B2,ROW(INDIRECT("’"&A2&"’!\$A\$2:\$A\$416"))-ROW(‘NBC Sports’!\$B\$1)),SUMPRODUCT((B\$2:B2=B2)*(A\$2:A2=A2))),MATCH(C\$1,Style!\$A\$1:\$F\$1,0))

If you are pasting this formula in your file, please replace curly single quotes with straight single quotes.

• Tani Sylvester says:

Thanks Ashish! I just tried it and it works beautifully!! If I want to add other Open items from additional tabs (ie E, News, etc), how should I modify the formula. I tried it with Sports and Style and it works, but it I want to add E or any other tab, how would I go about doing it? Thank you so much! 🙂

• You are welcome. The formula should work for all tabs as long as the spellings match.

• Tani Sylvester says:

Actually, I just tried your formula with different brands and it works!! You are amazing, again. Are you based in LA? Would love for you to give a presentation for our company.

• Tani Sylvester says:

Hi Ashish,
One last question.

Let’s say in my All Brands tab, in addition to populating the Open Items column (C), I also want to populate Columns D-H (Status/Note, Owners, Date Updated and Priority), with data from the various tabs.

How would I modify your formula so that in addition to populating Items from multiple ranges, I can also populate other columns?

• Hi,

You simply need to copy the formula to your right. The MATCH(C\$1,Style!\$A\$1:\$F\$1,0) portion of the formula reads the heading in row 1 and determines the column to pick up the data from.

• Tani Sylvester says:

It worked!! Thanks again

• Tani Sylvester says:

Hi Ashish,
I have one more question that requires your expertise! 🙂

I have transferred your formula to another doc and using it there. It works beautifully, but the only thing I’m having trouble with is adding an AND condition. Please see below:

1. Let’s say I have multiple brand tabs (Bravo, NBC, USA, Oxy, Syfy) with consistent header rows (Series, Ep#, Air Date, etc).
2. I am currently working on a tab titled ‘All Brands’ in which I want to consolidate data from the multiple tabs
3. Col A in my ‘All Brands’ tab has the network name (which exactly matches the tab name), column B has my Flight Start, column C has my Flight End. In Column D (‘All Brands’), I am looking to pull all series indicated in the brand (col A) that has an Air Date (col F in the individual brand tabs) that fit between the flight start and flight end (indicated in Col B & C of ‘All Brands). To do this, I want to slightly modify your formula and add an And condition –
If there are series whose air date is greater than or equal to the Flight Start and less than or equal to the Flight End indicated in ‘All Brands’, place here.
Here’s what I tried and it’s not working:

=INDEX(INDIRECT(“‘”&A2&”‘!\$A\$2:\$J\$416”),SMALL(IF(AND(INDIRECT(“‘”&A2&”‘!\$F\$2:\$F\$416”)>=\$B2,(INDIRECT(“‘”&A2&”‘!\$F\$2:\$F\$416”)<=C2)), ROW(INDIRECT("'"&A2&"'!\$A\$2:\$A\$416"))-ROW(NBC!\$B\$1)),SUMPRODUCT((B\$2:B2=B2)*(A\$2:A2=A2))),MATCH(D\$1,USA!\$A\$1:\$J\$1,0))

• Hi,

Try this array formula (Ctrl+Shift+Enter) in cell D2 of All Brands sheet. You may copy this down and to the right.

=INDEX(INDIRECT("’"&\$A2&"’!\$A\$2:\$J\$416"),SMALL(IF(INDIRECT("’"&\$A2&"’!\$F\$2:\$F\$416")>=\$B2,IF(INDIRECT("’"&\$A2&"’!\$F\$2:\$F\$416")<=\$C2,ROW(INDIRECT("'"&\$A2&"'!\$A\$2:\$A\$416"))-ROW(NBC!\$B\$1))),SUMPRODUCT((B\$2:B2=B2)*(\$A\$2:\$A2=\$A2))),MATCH(D\$1,USA!\$A\$1:\$J\$1,0)) Hope this helps.

• Tani Sylvester says:

Ashish,
As always, you are amazing! The formula worked beautifully – thank you again. How did you learn Excel? I am taking a formulas and functions course, but haven’t gotten as advanced as you obviously are. I’m familiar with the Index, Indirect and Row functions, but not necessarily how you have used them here.

• Hi,

Thank you for your kind words. Happy to help. I am just about very mediocre. I just practice problem solving everyday to improve my skills.

All the best.

• Haque says:

two sheets: one is data sheet, another is Search Result Sheet. What I
want is I will type a subject Code in A1 of Search Result sheet and
the ROLLs (in Data sheet) related to this will be filled down in A
column of Search Result sheet. In this way I will put other codes in B, C, D……..

I have given two examples in Search Result sheet.
Please frame the Formula in such a way that I can use it in other contexts.

Regards
Haque

• Hi,

You may refer to my solution in the worksheet named Ashish’s solution at the following link.

This is an array formula based solution. Please read my note in the blue box on the worksheet named Ashish’s solution.

If the solution is cumbersome in terms of taking time to process, then post back. I will design a solution using VBA and Advanced Filters.

Hope this helps.

• Haque says:

Hi,

Thank you very much

• Hi,

In cell L2, enter this array formula (Ctrl+Shift+Enter) and copy down

=IFERROR(INDEX(\$G\$3:\$G\$14,SMALL(IF(\$A\$3:\$A\$14=LOOKUP(2,1/(K\$3:K3<>""),\$A\$3:\$A\$14),ROW(\$A\$3:\$A\$14)-ROW(\$A\$2)),ROW(1:1)),1),"")

• g says:

hi,
in summary – the excel file has 3 worksheets
1.worksheet 1 is the main player report
2.worksheet 2 is the expected plays – what the players were expected to play – which games and what the score was..each player can play several games
3.worksheet is the actual plays – what the players actually played – which games and what scores ..again each player played several games –that were different from expected.

Desired Result:
1.I have in the main player report the list of constants that exist in both expected and actual which are: below;
PLAYER’S NAME PLAYER ID POSITION COACH NAME TEAM
The rest I would like extracted from each worksheet.

Desired Result for extraction from each worksheet:
Entries to Appear in Individual Cells:
(a) I directly want all entries to appear in individual cells instead of getting them first into one and then splitting them.
Desired Result for sum
Total Sum of Each Player’s Scores Listed (Total Sum for Expected/Actual Score per player)
(b) total sum of each player’ actual game scores and total sum of each player’s expected scores listed. I directly want all expected sum and actual sum entries players totals to appear. In other words I want to know how much each player’s expected score was and actual score was per game. This will help give awards based on scores and assess which future games to play.
Desired Result for game name and scores to appear
2.I would like expected game and score extracted from the expected worksheet for each player

3.Also, I would like the actual game and score extracted from the actual worksheet for each player
Desired Result for game names to identified correctly
4.Each player will have the expected games and scores and the actual games and scores
(a) For the games, Los Angeles and St. Catharines is one game respectively. Currently, they are separated. They are Los and Angeles and St and Catharines. Can this be corrected? Specifically, Los Angeles be identified as one game and St. Catharines be identified as one game?
Desired Result to assess if expected game = actual game
Expected Games Vs. Actual Games
5. If they played the games they were expected to play, can you insert a formula such as if expected game = actual game conditionally format the expected game to green or add a column at the end with a check mark? I am looking for players who actually played the expected game. This will help assess quickly why they did not play the game they should have played and schedule games better in the future.

any player could be playing several games different from what they were expected to play so it is not a classic v look up

https://skydrive.live.com/redir?resid=93D5E215410A4CEC!125&authkey=!ACT01q4PVxOuU_c

for above link – I have added a comment in cell f11 and i13 to provide illustration of what I am referring to regarding splitting of game names incorrectly (Los Angeles or St. Catharines are considered 2 games)

• Hi,

You may refer to my solution in this workbook

Click on the square box 2 at the top left of the workbook to expand columns. The columns so unhidden will show you individual games and scores. Have also applied conditional formatting to compare Actual games played with Expected games played. I have also corrected for the comma problem in Los Angeles.

Let me know how it works.

• g says:

hi Sir Ashish;

I have a minor request.

in order to get actual data, I only want to use player name and id as a constant . Since in actual data the remainder of the constants do not apply.

so it is a smaller formula. Can you adjust?

Thanks

• Hi,

The existing formula only uses Player id as the lookup_value (it does not take Player Name into account at all) for generating Actual as well as Expected data. I doubt whether I can make the formula any smaller that what it already is.

• g says:

I just need to search by player id and player name only so columns c/d/e eliminated

• Hi,

Delete columns C, D and E from all the three sheets. You may (if you wish) delete it from only the Roster sheet as well (leaving column C, D and E) on the other two sheets. In either case, the solution will remain unchanged.

• g says:

Completed!

Thank you sir for your tremendous Excel help..

I have submitted my assignment with pride and received much thanks!!

• sam says:

Hi, I want to use vlookup on a column where duplicate values exists. In the Index Column, there are rows with hyphen value and I want vlookup to move to the next row when there is a hyphen and pick the value from the row where actual value exists. is it possible?. here is the example

In the below sample table, A493-1 repeated in the column A and I want to skip the first occurrence because it has ‘-‘ value. I would like to pick the value in the next row. Thanks for the help…

ID Dev Manager Tool
A492-1 Joe Smith CVS
A492-7 John Smith CVS
A492-8 Joe Smith JIRA
A493-1 John Smith –
A493-1 Joe Smith JIRA
A493-3 John Smith –
A493-3 Joe Smith CVS
A493-5 John Smith –
A493-5 Joe Smith CVS
A493-6 John Smith JIRA

• Hi,

Try this

1. Suppose your data is in range A1:C11 of worksheet named Actual Data. Headings are in A1:C1
2. In another worksheet named lookup, your ID’s are listed from cell A3 downwards
3. In cell B3 of the lookup worksheet, enter the following formula and copy down

=INDEX(‘Actual Data’!\$C\$2:\$C\$11,MAX(INDEX((‘Actual Data’!\$A\$2:\$A\$11=Lookup!A3)*(CODE(‘Actual Data’!\$C\$2:\$C\$11)<>45)*(ROW(‘Actual Data’!\$A\$2:\$A\$11)-ROW(‘Actual Data’!\$A\$1)),,)),1)

Hope this helps.

• sam says:

hi all i have

A3=pete

A4=sam

A5=perkins

A6=sam

A7=sam

A8=perkins

A9=dave

A10=perkins

A11=sam

A12=louis

i would like to make a search string in B3:B11 so that if i enter for example sam and perkins i get successively and ordinally all the items related to it like:

C1=sam

C2=sam

C3=sam

C4=sam

C5=perkins

C6=perkins

C7=perkins

if there is aformula suggested will it work also for numbers ?? this is my issue …any ideas??

ashish can u please assist a bit ?… after a long thinking i managed to do the folowing :

i wrote in cell F3=INDEX(\$A\$3:\$A\$12,MATCH(SMALL(COUNTIF(\$A\$3:\$A\$12,”<"&\$A\$3:\$A\$12),ROW(A1)),COUNTIF(\$A\$3:\$A\$12,"<"&\$A\$3:\$A\$12),0)) ctrl+shift+enter and i dragged down to cell F12

then i made my search string from i3:i7 then i wrote in L3=INDEX(\$F\$3:\$F\$12,SMALL(IF(ISNUMBER(SEARCH(TRANSPOSE(IF(\$I\$3:\$I\$7″”,\$I\$3:\$I\$7)),\$F\$3:\$F\$12)),ROW(\$A\$3:\$A\$12)-MIN(ROW(\$A\$3:\$A\$12))+1),ROW(A1))) ctrl+shift+enter

NOW MY BIG issue : im trying to replace INDEX(\$F\$3:\$F\$12 and TRANSPOSE(IF(\$I\$3:\$I\$7″”,\$I\$3:\$I\$7)),\$F\$3:\$F\$12)), with my formula located in F3 in order to avoid using \$F\$3:\$F\$12 …so i tried :INDEX(\$A\$3:\$A\$12,MATCH(SMALL(COUNTIF(\$A\$3:\$A\$12,”<"&\$A\$3:\$A\$12),ROW(\$A\$3:\$A\$12)),COUNTIF(\$A\$3:\$A\$12,"<"&\$A\$3:\$A\$12),0)) ……. but it wont work …is there a way ????

• Hi,

Try this

1. Suppose headings are in A2:B2 and they are in Bold face
2. Type Sam and Perkins in range B3:B4
2. Select A2:A12 and press Ctrl+T > OK
3. Select B2:B4 and press Ctrl+T > OK
4. In cell C3, enter the following array formula (Ctrl+Shift+Enter)

=IFERROR(INDEX(\$A\$3:\$A\$12,SMALL(IF(TRANSPOSE(\$B\$3:\$B\$4)=\$A\$3:\$A\$12,IF(TRANSPOSE(\$B\$3:\$B\$4)>=\$A\$3:\$A\$12,ROW(\$A\$3:\$A\$12)-ROW(A\$2))),ROW(1:1)),1),"")

5. Copy this formula down.

Hope this helps.

• Nelson says:

Been looking through all of this and have a question.

I have prodIDs on Row A1:A150 there can duplicate prodIDs per row

I have corresponding Part numbers in Col B1 – K150 associated to each PRODID

So how can I use your formula to generate a list of all part numbers in the array Col B1:K150 associated to the Unique ProdIDs list?

• Hi,

• Hi,

The file has all errors. I cannot make any sense of the file. Relook at your question and explain it very clearly with respect to data in the file.

• Nelson says:

Not sure what went wrong there, Col A are the partIDs I want to base the criteria off of and col B and beyond are the part options.

So using Col A as the criteria(parts ID), I need to list out all the associated parts Options (but no duplicates)

Thanks again for all your help, much appreciated

• mahendar kumar says:

Hi Ashish,

using VLOOKUP I get an error #N/A. for the below data

LOW HIGH INPUT OUTPUT R look UP
1 4 0 F R1 R2
6 10 8 T R2
11 13 0 F R3
16 20 0 F R4
25 30 26 T R5

LOW HIGH INPUT OUTPUT R look UP
1 4 0 F R1 #N/A
6 8 0 F R2
11 13 0 F R3
16 20 0 F R4
25 30 0 F R5

FORUMLAS:

D2:D6 –> =IF(AND(C2>=A2,C2 =VLOOKUP(“T”,D2:E6,2,FALSE)

————-
i am aware of this error,it occurs when there is no matching (lookup)value.

I dont want to see this error.Instead want FALSE or F as an o/p when the INPUT Column entered value doesn fall in the range.

Thank you!

• Hi,

• Mahendar Kumar says:

I got that Ashish!

I was looking for this formula :

=IF(ISNA(VLOOKUP(“TRUE”,D2:E53,1,FALSE)),”FALSE”,VLOOKUP(“TRUE”,D2:E53,1,FALSE))

Thank you!!

You are doing Amazing job!! 🙂

• Thank you for your kind words. If you are using MS Excel 2007 + versions, you may use this smaller formula

=IFERROR(VLOOKUP(“TRUE”,\$D\$2:\$E\$53,1,FALSE),”FALSE”)

• Fred says:

Problem: Data is provided in a vertical manner. I want to use” look up a value in a list and return multiple corresponding values” in a horizontal format in another tab in my worksheet. Formula (in red) provided in Help Article does not seem to work. Also I’m not sure how to copy it over.What part of the formula needs to be “locked?
I’m giving the arrays unique names (vs using your method of locked ranges.)
Using Excel 2003

original data
customer model price range/array names used:
123 A \$5 custprice = C9:E15
123 B \$8 custnum = C9:C15
123 C \$12
124 A \$6
124 D \$14
125 C \$13
125 D \$15

Desired format
customer 1st model 1st mod price 2nd model 2nd mod price 3rd model 3rd mod price
123 #VALUE! #VALUE!
124
125

• Fred says:

You must be using a newer version of Excel, as I got a file conversion message when I opened your file, and the file had no formulas once it converted to my version. It just showed {=#VALUE!). I’m not sure if the file conversion also clobbered other tabs. One of the issues i’m having is I want the lookup data on a separate tab from teh results.

I am using Excel 2003.

• Fred says:

yes – thank you, very very much. Huge help. I had some trouble copying the formula down until I realized you had named some ranges that had to be edited. Once I did that, it worked beautifully.

• Joe says:

Ok, here is the upload and I’ll try to explain what I’m trying to do a little better.

Background:
This workbook is supposed to provide a daily job listing for our work crews. They have set jobs for set days. On the Ramos tab (blue), the account manager will just select the day they are working on, 1-5. I used number instead of M, T, W because the work may be shifted due to holidays or special circumstances. Selecting the day, populates the account name and address on the Ramos tab, these cells are normally locked. Then if there is any additional work they are assigned, it is entered on the Add-Ons (green) tab. All of the info on the Ramos tab then feeds to the Today’s Work(orange) tab which is normally hidden. This then feeds the Ramos Work(red) tab, which is printed and given to the work crew. Accounts are assigned crews, days and service order on the Account(yellow) tab.

Issue:
In the version uploaded, I have Ramos Day 1 selected. There is normally only 1 job that day. I added an additional 4 job to the Add-Ons tab for total of 5 jobs. But I only get 4. I want it to look for Ramos Day “x” jobs on the Accounts tab and then looked for Add-On jobs starting with the first instance.

Hope that was a little clearer.

http://sdrv.ms/13mxSXT

• Jack Sons says:

In column B are names (in each cell one word or a blank) in rows 3 to 18.

In column AC in the corresponding cells are formulae resulting in positive or negative numbers or blanks or zeroes.

In cell C26 I want a list of those names in column B for which the corresponding cells in column AC show a number larger than 95000.

By “list” I mean that in C26 I want something like John — Paul — Sarah — William — Esther

Can this be obtained with an array formula? If not, what formula will do the trick?

Jack Sons.

• Hi,

Try this

1. Download and install the morefunc add-in from the following link on my website – https://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/. If you are using Excel 2010 or higher version, then please follow the steps mentioned in step 4
2. Once downloaded and installed, use the following array formula (Ctrl+Shift+Enter) in cell C26

=SUBSTITUTE(TRIM(SUBSTITUTE(MCONCAT(IF(\$AC\$3:\$AC\$17>=B26,\$B\$3:\$B\$17,””),” “),0,””)),” “,”,”)

B26 has 95000

Hope this helps.

• Jack Sons says:

I get during downloading a message saying that morefunc contains a virus because of which it has been removed. What now?
Is MCONCAT really necessary, is it possible to use CONCAT?

Jack.

• There is no virus in that download for sure. MCONCAT is necessary. No.

• Rajender says:

Hi All,

I want to lookup the value from a particular column having repeated values :-

for example : from cell A1: B6

Name Month Commision
A Jan 12
B Jan 123
C Jan 3
A Jan 211
B Feb 11

I tried below formula but unable to show the result having criteria in E2 & F2 :-
and formula in cell G2
Name Repeated Salary
a 1 =SUMPRODUCT((\$E\$2=\$A\$2:\$A\$6)*COUNTIF(\$A\$2:A2,A2)=\$F\$2,\$C\$2:\$C\$6) also I tried the formula =INDEX(\$A\$1:\$C\$6,MATCH(E2,\$A\$2:\$A\$6,0)*COUNTIF(\$A\$2:A2,A2)=\$F\$2,0).

Please sugggest what modifications required in the above formula or its all wrong ?

Regards,
Rajender

• Rajender says:

Hi Ashish,

Thanks a lot, its working fine.
Could you please assist how the below portion of the function works ?
ROW(INDIRECT(“1:”&ROWS(\$C\$1:\$C\$6))))=0,10000,
and how the above calculations works ?

I am unable to understand the calculation part of the above , how it calculates step by step ?

or Is there any link where I can find this type of calculation step by step to understand easily ?

Regards,
Rajender

• Hi,

You are welcome. The best way to understand step by step calculations would be by using the F9 key (highlight the portion of the formula which you cannot understand and press the F9 key). The F9 key will show you the result instead of the formula.

Also, the explanation of a similar problem be studied from here

Hope this helps.

• Richard says:

Hi,

Question & Requirement

When I enter the “Menu Name” in the cell ‘B2’, the Menu Code is generated automatically in the cell ‘F2’ and the menu name appears automatically in the cell ‘B5’ also. (Actually I have planned this Item Menu Master List to be in a separate sheet.)
I have planned the following
(1) Menu Name & Menu Code may not be sorted as any item i want to add or remove will be done from the ‘Item Menu Master’.

The items listed for the ‘Menu Name’ selected should list all the list of items (multiple occurrences) in the space provided above. (For Eg. If I select Item Code as ‘102’, then it should list two items from specific columns of ‘Item Menu Master’ above.

Item Name Chicken Biriyani Menu Code 101

Item List for above Menu Code
Measured For : 1 Kg
Item(s) Qty Unit Rate Amount
Chicken 0.75 Gms 1 0.75
Onion 0.15 Gms 25 3.75
Tomato 0.15 Gms 0.89 0.13
Basmati Rice 1 Kg(s) 96 96.00

101 Chicken Biriyani Chicken 1 Kg 0.750 Gms 1.00
101 Chicken Biriyani Onion 1 Kg 0.150 Gms 25.00
101 Chicken Biriyani Tomato 1 Kg 0.150 Gms 0.89
101 Chicken Biriyani Basmati Rice 1 Kg 1.000 Kg(s) 96.00
102 Alloo Parotta Potato 1 Kg 1.000 Kg(s) 28.00
102 Alloo Parotta Atta 1 Kg 1.000 Kg(s) 39.00

I am attaching the file. Please chek the reply mail in which I have send the xl file.

Thanks

Richard

• Hi,

• Richard says:

Wow,

Thanks. its working.

I will try all other options. can I have a material or youtube video to explain me this formula usage.

could u pls let me know the link.

Thanks

Richard

• Hi,

You are welcome. Here is the link where I have explained the formula.

Hope this helps.

• Gene says:

Lookup and Return Names With Non- Consecutive Rows

Having an issue with an array formula the community helped me with previously. Have been given a new wrinkle in the data. I was using an array formula because all the data was being captured. {IFERROR(INDEX(‘sheet2’!\$A\$2:\$A\$14,SMALL(IF(‘sheet2’!\$C\$2:\$C\$14=”X”,ROW(‘sheet2’!\$A\$2:\$A\$14)-ROW(‘sheet2’!\$A\$1)),C\$2),1),””)}. But now I have to skip certain rows. I’ve tried using my current array formula but can’t get it to work. {IFERROR(INDEX(‘sheet2’!\$A\$2:\$A\$5,\$A\$9:\$A\$14:SMALL(IF(‘sheet2’!\$C\$2:\$C\$5,\$C\$9:\$C\$14=”X”,ROW(‘sheet2’!\$A\$2:\$A\$5,\$A\$9:\$A\$14)-ROW(‘sheet2’!\$A\$1)),C\$2),1),””)}. Can this still be done with an array formula? I need to skip rows 6, 7, 8.

SHEET 1
1 Job Titles Dept. 1 2 3 4
2 Claims CSI L. Smith
3 Outb Calls CSI C. Pepper
4 Notifications CSI A. Hawkins J. Ramsey
5 Evaluator CSI

SHEET 2
1 Staff Name Mgr Claims Outb Calls Notifications Evaluator
2 A. Hawkins Adams x x
6 J. Arcomone Lewis
7 J. Rast Lewis x
8 J. Montalvo Lewis x
9 J.Ramsey Jones x
10 K. Selman Jones
11 L. Smith Jones x
12 L.Young Jones
13 M. McBride Jones
14 R. Wilson Jones

• Hi,

• Dave Whitney says:

I have a table as shown in the picture below where various ID are ranked 1-5 over a range of 9 skills

Image

Desire output where each skill shows those with same ranking in groups

column 1 has header of ID any number of unique ID, say ID1 – ID10
Other column each a skill name, see http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/not-sure-best-way-to-get-the-results-i-need-from-a/7d9feb0d-6bcc-4b5f-b486-9d76c8e687fb for pictures

Should a pivot table be able to do this, so far none of my attempts are even coming close.

• Hi,

You may refer to my solution in this workbook.

If you are using Excel 2010 and higher versions, then please read the point 4 of the link very carefully.

Hope this helps.

• Melwan says:

Hi,

I’m using this formula here from F5-F24:

=IF(ISERROR(INDEX(Sheet2!\$A\$2:\$B\$30,SMALL(IF(Sheet2!\$A\$2:\$A\$30=\$C\$2,ROW(Sheet2!\$A\$2:\$A\$30)),ROW(1:1)),2)),””,INDEX(Sheet2!\$A\$2:\$B\$30,SMALL(IF(Sheet2!\$A\$2:\$A\$30=\$C\$2,ROW(Sheet2!\$A\$2:\$A\$30)),ROW(1:1)),2))

and it’s working well except for a small detail. The returning results are complete but include duplicates. Is there a way to include only unique results?

Thank you very much.

• Hi,

• Pawan says:

Hi all….
Below is the data, in Col A, 37 value is repeating with different value in col B.

Row # Col A Col b
1 25 a
2 37 b
3 15 c
4 37 d
5 21 e
6 37 g
7 56 h
8
9
10
11 25 a
12 37 b
13 15 c
14 37 b
15 21 e
16 37 b
17 56 h

from cell A11 i have enter same value and as above mention but i didn’t get same value of col B in cell B11, I used Vlookup but i get first found value (B against 37 data).
Also index match function given the same value.

I want same data as above

• Hi,

In cell B11, enter this array formula (Ctrl+Shift+Enter) and copy down

=IF(ISERROR(INDEX(\$A\$2:\$B\$8,SMALL(IF(\$A\$2:\$A\$8=\$A11,ROW(\$A\$2:\$A\$8)-ROW(\$B\$1)),COUNTIF(A\$11:A11,A11)),2)),””,INDEX(\$A\$2:\$B\$8,SMALL(IF(\$A\$2:\$A\$8=\$A11,ROW(\$A\$2:\$A\$8)-ROW(\$B\$1)),COUNTIF(A\$11:A11,A11)),2))

Hope this helps.

• James says:

Hi Ashish

Thanks for your tutorial on how to return multiple corresponding values based on a cell reference.

http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx

I can get your formula to work when I keep it on A1:A7 but when I move the array table to another section of the spreadsheet eg., H11:H20 I just cannot make it work.
I’ve tried changing the column number, the row selections – no luck.

If you are able to help at all I would really appreciate it.

James

Here’s the spread I’m working on in case you want to take a look.
https://www.dropbox.com/s/b1u5z1cmcf0hhqi/Checklist.xlsx

• Hi,

In cell E36, enter this array formula (Ctrl+Shift+Enter) and then copy down

=IFERROR(INDEX(\$H\$9:\$I\$21,SMALL(IF(\$H\$9:\$H\$21=\$E\$34,ROW(\$H\$9:\$H\$21)-ROW(\$H\$8)),ROW(1:1)),2),””)

Hope this helps.

• James says:

Thank you so much.

I was missing that -ROW bit in the IF formula.
Still not sure how it works but glad it’s working.

• Aboobacker says:

Sir
I have a table like given below
I want to find a particular ID for know how much given him and i want know the date also

can you help me

Date Id Name Amount
01-01-14 A10 Abilash 100
28-01-14 A10 Abilash 150
02-02-14 C10 Chandu 120
23-02-14 A10 Abilash 200
01-03-14 C10 Chandu 150

• Hi,

Do you want to know the total amount which was given to each person? If yes, then if the individual amounts were given on different dates, then which data would you like to show. Please share your expected result.

• Victor says:

Hi Ashish,

Thank you for the very informative video. I had no idea Power Query existed beforehand. Now I’m attempting to apply it to my work.

Is it possible to have two Power Query tables side by side in one spreadsheet? I found that Excel won’t let me refresh both tables, just one.

Victor

• Hi,

You are welcome. I am glad you found the video informative. You can place two Power Query tables side by side. Go to Data > Refresh > Refresh All to refresh all connections in the workbook.

• sreenath says:

Table 1 Table 2
Amount Amount 53 60 115 101 141 100 68 107 181 98 170 188 79 120 117 96 155 51 123 185
185
123
51 9. Using formula, reverse amount in Table 1 as shown above in Table 3
155
96 Table 3
117 Amount
120
79
188
170
98
181
107
68
100
141
101
115
60
53

• Hi,

Neither have you bothered to describe your question, nor does your data appear correctly. Furthermore, courtesy demands that you atleast start a conversation with a Hi.

• RobC says:

Helo Ashish,

I hope that I can explain myself well enough without providing an example cause I don’t want to waste your time but it’s the only way I can do that for now, so here I go.

What I am needing to do is have this wonderful code search two seperate areas for the “key” paramater which is a date and return all the items found in area one and then start over in area two once it is completed with area one.

Here is how I “tried” to modify it…

=IF(ISERROR(INDEX(\$D\$2:\$F\$202,SMALL(IF(\$D\$2:\$F\$202=N\$2,ROW(\$D\$2:\$F\$202)-ROW(\$F\$1)),ROW(\$A1:\$C1)),1))=FALSE,CONCATENATE(“A”,INDEX(\$D\$2:\$F\$202,SMALL(IF(\$D\$2:\$F\$202=N\$2,ROW(\$D\$2:\$F\$202)-ROW(\$F\$1)),ROW(\$A1:\$C1)),1),” @ “,INDEX(\$D\$2:\$F\$202,SMALL(IF(\$D\$2:\$F\$202=N\$2,ROW(\$D\$2:\$F\$202)-ROW(\$F\$1)),ROW(\$A1:\$C1)),3)),IF(ISERROR(INDEX(\$G\$2:\$I\$201,SMALL(IF(\$G\$2:\$I\$201=N\$2,ROW(\$G\$2:\$I\$201)-ROW(\$I\$1)),ROW(\$A1:\$C1)),1))=FALSE,CONCATENATE(“B”,INDEX(\$G\$2:\$I\$201,SMALL(IF(\$G\$2:\$I\$201=N\$2,ROW(\$G\$2:\$I\$201)-ROW(\$I\$1)),ROW(\$A1:\$C1)),1),” @ “,INDEX(\$G\$2:\$I\$201,SMALL(IF(\$G\$2:\$I\$201=N\$2,ROW(\$G\$2:\$I\$201)-ROW(\$I\$1)),ROW(\$A1:\$C1)),3)),””))

N\$2 Is any given date
The “A” & “B” is my Team Idents (They each perform specific tasks so on a given day one or both teams may have work as well as visit the same customer)
Col “D” & “G” has Unique Customer Codes
Col “E” & “H” has the date to search for
Col “F” & “I” has the times of that date that the teams will perform work

So My Output I am looking for is…

Jul. 22, 2014
A232 @ 11:50PM
A234 @ 8:15PM
B232 @ 10:00AM
B375 @ 2:00PM

My Problem with the above modified code is that “IF” there is a date that Team “A” & “B” has work to do it’ll only show Team “A” but if team “A” does not have work on that day then it will certainly show Team “B”s work but I cannot get it to show both teams work on the same date.

Eventually I would like to have three tabs for this. Tab 1 for the dates with outlines; Tab 2 for Team “A”s workload; Tab 3 for Team “B”s workload.

Thank You So Much for Your Time and Any Help Possible!

~Rob

• Hi,

• Reuven says:

Hi Ashish,

I am amazed by your great knowledge, I have a very similar problem but I only need the total of all the same item in 1 cell, in your example it would be for Ashish 1200 ( i don’t want to see the breakdown)
This is my formula
=IFERROR(INDEX(‘all PO”s’!\$C\$2:\$C\$39,MATCH(\$B9&\$A9&D\$1,’all PO”s’!\$I\$2:\$I\$39,FALSE),1),””)
the problem is it only returns the first match and I have multiple matches and I need the total.
I would love to send you the whole file, where can I send it?
Reuven

• Hi,

Thank you for your kind words. From your description, it looks like you may to use the SUMIF() or SUMPRODUCT() function. If you need more specific help, then share the link from where I can download the file. Please clearly mark the cell(s) where you want the result and also show the expected result.

• Thanks again and as per your request I shared the file in the following link,
As you can see in the “Main sheet” I have the same formula multiple times and it takes the info from “All PO’s” the problem is at ‘main sheet’ cell D9 (highlighted in red) it needs to take info from ‘all PO’s’ cell C23+C24 (because they both have the same item code in collumn I) and it only gives me C23 so if you can please tell me how to fix it i would really appreciate it.

• Hi,

Try either of the following in cell D9 of main sheet and then copy down/right

1. =SUMPRODUCT((‘all PO”s’!\$G\$2:\$G\$39=’main sheet’!\$A9)*(‘all PO”s’!\$A\$2:\$A\$39=’main sheet’!\$B9)*(‘all PO”s’!\$H\$2:\$H\$39=’main sheet’!D\$1)*(‘all PO”s’!\$C\$2:\$C\$39)); or

2. =SUMIFS(‘all PO”s’!\$C\$2:\$C\$39,’all PO”s’!\$G\$2:\$G\$39,’main sheet’!\$A9,’all PO”s’!\$A\$2:\$A\$39,’main sheet’!\$B9,’all PO”s’!\$H\$2:\$H\$39,’main sheet’!D\$1)

Hope this helps.

• amazing amazing how did you do it, can you explain a bit more how this works

• Hi,

You are welcome. Please read up on the SUMIFS() function in the Help menu of MS Excel.

• Reuven says:

Thanks again, I used it and it works greatly, however you created the range to only work til row #39 i need it to be for the whole columns of each, so I tried to change the formula slightly to
=SUMPRODUCT((‘all PO”s’!G:G=’main sheet’!\$A9)*’all PO”s’!A:A=’main sheet’!\$B9)*(‘all PO”s’!H:H=’main sheet’!D\$1)*(‘all PO”s’!C:C))
or i tried =SUMIFS(‘all PO”s’!C:C,’all PO”s’!G:G,’main sheet’!\$A9,’all PO”s’!A:A,’all PO”s’!H:H,’main sheet’!D\$1)
and in both cases it returns an error.
so I had no choice so i changed it to
=SUMPRODUCT((‘all PO”s’!\$G\$2:\$G\$5000=’main sheet’!\$A9)*(‘all PO”s’!\$A\$2:\$A\$5000=’main sheet’!\$B9)*(‘all PO”s’!\$H\$2:\$H\$5000=’main sheet’!D\$1)*(‘all PO”s’!\$C\$2:\$C\$5000))
which is fine but not ideal, is there another way to do it?

• Hi,

Ensure that the range of the formula is till row 39 only. This range can be made to auto expand if convert the range into a Table. Select A1:I39 of the all PO’s sheet and press Ctrl+T > OK. Now as data will be added after row 39, the range of your formula will keep expanding automatically.

Hope this helps.

• Reuven says:

Thanks again, and as usual you are truly amazing

• Hi,

You are welcome. Thank you for the feedback.

• Sunil says:

Dear Sir,

For same example, i want between two dates lookup.

Could you help.?

• Hi,

• Sunil says:

Dear Sir,

in this workbook i have data of dispatch details, for which i have put sum-ifs function to sum up the Qty’s of a particular product for the particular period(between two dates), in the same way i want to get the results of invoice no’s in the summary sheet against which the products are dispatched in the particular period(between two dates) on the summary sheet.

Hope you’ll get what I’m trying to tell.

Sunil Kumar .N

• Sunil says:

Thank you Sir, its working great help by you.

• Sunil says:

SIR, BUT I’M NOT GETTING ALL THE INVOICE NO’S, MEANS IT SHOULD EXCLUDE THE DUPLICATE VALUES AND RETURN UNIQUE VALUES.

KINDLY HELP.

• Hi,

You may refer to my solution at this link.

• Sunil says:

Sir,

in that Small Change is there, The Invoice No’s are those which are in B2:B1002 column, i want those to come in invoice columns.

• Scott Reid says:

This is such a great resource! Ashish, you are awesome!

I am trying to return to All Shifts tab the rows that would contain a percentage equal to or greater than 95%. See the below. I have tried multiple variations of the formula’s here and can’t seem to get it. Any help would be appreciated!

All Shifts Name First Round Second Round Disparity
Shift 1 Joe 26 25 96.15%
Shift 1 Ken 41 38 92.68%
Shift 2 Mark 56 55 98.21%
Shift 3 John 27 26 96.30%

Shift 1
Name First Round Second Round Disparity
Joe 26 25 96.15%
Mary 55 38 69.09%
Ken 41 38 92.68%

Shift 2
Name First Round Second Round Disparity
Mark 56 55 98.21%
Larry 25 21 84.00%
Sue 26 20 76.92%

Shift 3
Name First Round Second Round Disparity
Tom 40 25 62.50%
Mellissa 11 8 72.73%
John 27 26 96.30%

• Scott Reid says:

Ok. I appreciate the feedback. I need the sheets separate for nurse rounds.

How about if I used three lookups as in the below – one for each sheet. Is this possible to pull this data onto one separate sheet?

Thank you again!

Shift 1 Name First Round Second Round Disparity
Shift 1 Joe 26 25 96.15%
Shift 1 Ken 41 38 92.68%

Shift 2 Name First Round Second Round Disparity
Shift 2 Mark 56 55 98.21%

Shift 3 Name First Round Second Round Disparity
Shift 3 John 27 26 96.30%

• Hi,

No that will still not be possible. For my formula to work, all data should be on one worksheet. So, we will let your three worksheets remain intact and dynamically append data from multiple worksheets into a single one (via the method described in the link shared in my previous post).

• Scott Reid says:

So what you are saying is I can’t build a formula that will look into the other sheets (tabs) and return the rows where the percentage is greater than 95%?

I really don’t want a pivot table. I need to be able to have the shifts update from the rounds and, hopefully, have it displayed in the Cover sheet.

Thanks again.

• Hi,

Yes. That is what I am saying. First append all data in one location (either in another worksheet or in Power Query) and then apply a filter.

• Scott Reid says:

Thanks for the help. I will mess with it to do what I need. There might be a VBA function that I can program that might do what I need.

Keep up the good work!

• Angel says:

Hello Ashish,

=IF(ISERROR(INDEX(\$B\$3:\$C\$9,SMALL(IF(\$B\$3:\$B\$9=\$B\$12,ROW(\$B\$3:\$B\$9)-ROW(\$C\$2)),ROW(A1:C1)),2)),””,INDEX(\$B\$3:\$C\$9,SMALL(IF(\$B\$3:\$B\$9=\$B\$12,ROW(\$B\$3:\$B\$9)-ROW(\$C\$2)),ROW(A1:C1)),2))

• Hi,

I cannot point out a mistake with your formula. Just ensure the following:

1. That you confirm the formula with Ctrl+Shift+Enter (after pressing F2); and
2. Spelling of the entry in cell F2 should match the spelling of entries in range B3:B9 (there should be no extra spaces)

If it still does not help, then upload the file to OneDrive and share the link of the file here.

• Sammy says:

Hi Ashish,

Please see the following and let me know if you can help!

On the next page I have Bill of material numbers 35 and 987 in column A.

Search Here Data populates in these cells

So for example if I type 35 in the search area it should draw the data and look like this.

35 71920 Nuts
98789 Bolts
58245 Ubolts
123 Screws
786 Washers
987485 L Bracket
2546 U Bracket
35896 S Bracket
5468 Hinge
98765412 Iron Rod
987789 Copper Rod

If I type 987 in the search area it should look like this.

987 89654 Pine Dowel
7541 Cedar Dowel
685 Maple Dowel
25454 Oak Dowel
25456 Iron Dowel

The data would be stored on another page in the same workbook, but I need it to draw to certain cells on a seperate by only typing in the Bill of Materials Number.

• Hi,

Your question is not clear. Upload your workbook to OneDrive and share the link of the workbook here. Please also give a detailed description of the expected result.

• Hi,

You may refer to my solution in this workbook. Type the material number in cell B6 and data in range D6:E18 would update.

Hope this helps.

• Ankur Goswami says:

Hi Ashish,
I have been trying to make one template, where I will have 2 sheets under same workbook.

In sheet 1, I will enter the field value based on which Excel should search for relevant strings or rows in sheet 2 and then in sheet one it will populate all the values.

I am using the following formula but for some reason its not working.

=IF(ISERROR(INDEX(‘BPO Structure Dictionary’!\$H\$3:\$AE\$293,SMALL(IF(‘BPO Structure Dictionary’!\$H\$3:\$AE\$292=\$F\$4,ROW(‘BPO Structure Dictionary’!\$H\$3:\$AE\$291)),ROW(‘BPO Structure Dictionary’!1:1)),2)),””,INDEX(‘BPO Structure Dictionary’!\$H\$3:\$AE\$292,SMALL(IF(‘BPO Structure Dictionary’!\$H\$3:\$AE\$291=\$F\$4,ROW(‘BPO Structure Dictionary’!\$H\$3:\$AE\$292)),ROW(‘BPO Structure Dictionary’!1:1)),2))

Name of sheet 2 is BPO Structure Dictionary

• Hi,

Try this array formula (Ctrl+Shift+Enter)

=IF(ISERROR(INDEX(‘BPO Structure Dictionary’!\$H\$3:\$AE\$293,SMALL(IF(‘BPO Structure Dictionary’!\$H\$3:\$H\$292=\$F\$4,ROW(‘BPO Structure Dictionary’!\$H\$3:\$AE\$291)-ROW(‘BPO Structure Dictionary’!\$H\$2)),ROW(‘BPO Structure Dictionary’!1:1)),2)),””,INDEX(‘BPO Structure Dictionary’!\$H\$3:\$AE\$293,SMALL(IF(‘BPO Structure Dictionary’!\$H\$3:\$H\$292=\$F\$4,ROW(‘BPO Structure Dictionary’!\$H\$3:\$AE\$291)-ROW(‘BPO Structure Dictionary’!\$H\$2)),ROW(‘BPO Structure Dictionary’!1:1)),2))

• Khawar says:

Data range is given below. I am looking for formula which may help me
in looking up name against same value.

Data Range:

B2:B4 C2:C4
John 1
George 2
Stephen 2

Where Required:

B7:B9 C7:C9
1 ?
2 ?
2 ?

• Hi,

In cell C7, enter this array formula (Ctrl+Shift+Enter) and copy down

=IFERROR(INDEX(\$B\$2:\$B\$4,SMALL(IF(\$C\$2:\$C\$4=B7,ROW(\$B\$2:\$B\$4)-ROW(\$B\$1)),COUNTIF(B\$7:B7,B7)),1),””)

Hope this helps.

• Rod says:

Hi Ashish,

I am using a formula similar to the function that you are referencing about however I am having a small issue with it.

Acc No: Sub Acc
V958874 226243
V958874 246956
V958874 264197
V103234 241560
V103234 264020

I would like all values that reference V958874 (cell J4 in the formula) in the sub account returned.

I am getting the Sub Acc values returned however it should stop at 264197. The formula is not picking up the first value that corresponds to V958874 in sheet 4. It moves to the next value sort of like A1 gives back B2 cells corresponding result instead of giving B2 result.

Instead I am getting the first value 241560 returned even though the account number it references has changed.

The formula I am using is listed below;

=IF(ISERROR(INDEX(Sheet4!\$A\$2:\$B\$11805,SMALL(IF(Sheet4!\$A\$2:\$B\$11805=\$J\$4,ROW(Sheet4!\$A\$2:\$B\$11805)),ROW(1:1)),2)),””,INDEX(Sheet4!\$A\$2:\$B\$11805,SMALL(IF(Sheet4!\$A\$2:\$B\$11805=\$J\$4,ROW(Sheet4!\$A\$2:\$B\$11805)),ROW(1:1)),2))

Is there a better formula or way of doing this?

Can you see an error in the formula that I am using to be giving me this result?

Thanks if you are able to help.

• Hi,

Try this array formula (Ctrl+Shift+Enter)

=IFERROR(INDEX(Sheet4!\$A\$2:\$B\$11805,SMALL(IF(Sheet4!\$A\$2:\$A\$11805=\$J\$4,ROW(Sheet4!\$A\$2:\$B\$11805)-ROW(Sheet4!\$A\$1)),ROW(1:1)),1),””)

Hope this helps.

• Rod says:

Hi Ashish,

Thanks for coming back to me.

I tried the formula and it didn’t work for me.

The value that your formula returned was the value that I am referencing in cell \$J\$4 on sheet1. The value \$J\$4 is the Acc No:V958874. this value also appears in Sheet4 Column A. (I am using sheet1 to have the results from sheet4 returned).

I need the value that is in sheet4 column B that corresponds to V958874 returned, as per my original example.

The formula that I pasted does work, however it misses the first value in column B that the value first references for some reason. it does return the first value in the next reference after the formula has pasted all relevant matches to the V958874.
In my example mentioned the value 241560 from sheet4 column B is returned at the end. This should not happen as the reference for this number in column A is V103234

I hope this is clear.

Are you able to help?

Thanks,
Rod

• Hi,

Upload the workbook to OneDrive and share the link of the workbook here.

• Hi,

In cell D21, enter this array formula (Ctrl+Shift+Enter) and copy down

=IFERROR(INDEX(Sheet4!\$A\$2:\$B\$11805,SMALL(IF(Sheet4!\$A\$2:\$B\$11805=\$J\$4,ROW(Sheet4!\$A\$2:\$B\$11805)-ROW(Sheet4!\$A\$1)),ROW(1:1)),2),””)

This is the same formula had suggested in my previous post as well.

Hope this helps.

• Rod says:

Hi Ashish,

You are a legend!

This time it worked perfectly.

I need to ask you another question on what I call a reverse v look up if you are able to help.

Sheet 1 is a template, I am looking up information from sheet 4 as you could see.

I would like to be able to enter information into sheet 1 and have it backed up in sheet 4.

I have been able to do this however it does not appear to be saving the way o would like.

I am entering a figure in sheet 1 Column C15. I have done a v lookup in sheet 4 column C. The figure appears next to the acc no and sub account number in sheet 4 column C.

I save the changes, then I enter another sub account number in sheet 1 column J5.

The figure in cell C15 now appears next to the new sub account number in sheet 4 column C, however the previous figure that was aligned next to the previous sub account number comes up as #N/A.

Is there a way that I can do this so that the figure from cell C15 stays in column C adjacent to the previous sub account number that I entered and save regardless of the new sub acc number being entered?

I eventually want to have a figure next to most sub acc numbers in sheet 4 column C.

I have created a link below for you to have a look if it is possible to help.

https://onedrive.live.com/redir?resid=662E642D16E6FD84!119&authkey=!AEcqnL3hzL-YU7w&ithint=file%2cxlsx

Thanks again.

Rod

• Hi,

No, that cannot happen. When Account numbers in column A change because of a change in cell J5 of sheet1, the value you are looking up in sheet4, undergoes a change and therefore the previous result is not retained.

• Rod says:

Thanks Ashish.

Do you know of any way around it?

Thanks,
Rod

• Brett says:

Hello Ashish,

The Power Query table that obtains multiple values from a single query is what I need across multiple worksheets.

I have monthly worksheets with an “expenses” table in each. There are over 120 worksheets. As an example, I would like to search all of them for 2014 to summarize gasoline expenses.

Unfortunately, Power Query does not seem to allow inputting an array of worksheets.

I currently have an array formula that only returns the first found and exact string only: {=VLOOKUP(B2,INDIRECT(“‘”&INDEX(SheetList,MATCH(1,–(COUNTIF(INDIRECT(“‘”&SheetList&”‘!\$C\$20:\$E\$55″),B2)>0),0))&”‘!\$C\$20:\$E\$55”),3,FALSE)}. Sheetlist is the worksheet array name on the query sheet, B2 is the query string, and c20:e55 is the table range to search across all worksheets I list in the array.

This formula does not do a partial string nor does it return multiple values in a table for all instances found, just the first value found after an exact string match.

What I would like is to submit a partial string query across worksheets ‘SheetA !’ thru ‘Sheet Z!’ and have the results appear in a table. This is just like your single worksheet example with Power Query, but across multiple worksheets. For example, the partial string “gas” would search the same table range in all worksheets and return the amounts entered in the dollar value column (column 3). Also, I would like it to allow a partial string search as the word gas appears with other descriptors such as the station name.

I’ve read this functionality does not exist in Power Query and requires VBA. Is there a solution?

Brett

• Hi,

Upload the workbook to OneDrive and share the link of the workbook here. Please also show the exact result which you are expecting.

• Vengi says:

Hi

I have been looking at your solution to search strings when they appear multiple time. I have however come across a couple of challenges.
I cannot find Power Query in my version of excel and I have tried to use the formula it is not working for me. I must be making a mistake somewhere.
Is Power Query not available to non-professional versions of excel?
I am happy to share my spreadsheet if you have time to assist.

Regards

• Hi,

Power Query can be downloaded free of cost from the Microsoft website and can be installed on any version (I think). Upload your workbook to OneDrive and share the link here.

• Alin says:

Hello,

Thank you for taking the time to answer our questions. Maybe you can help me too.
Your example is for finding Ashish from the column.
I have a column of numbers that I need to look it up in another column and return the values next to it. “=VLOOKUP(A2,\$C\$2:\$D\$300,2,false)” is not working. I get the same value for duplicates.
I have column A2:A500.
Then columns C2:C300 and D2:D300. In both columns A2:A500 and C2:C300 there are duplicates and vlookup will return only the first value.
Can you find solution?
Thank you,
Alin

• Hi,

• Mansoor says:

Hi Ashish,

I would need your help for below query:

In Excel, you have a datasheet with 5,000 rows and 40 columns. You have been asked to provide a single worksheet in a new workbook with only those records that have the value “TRUE” in Column Q. Explain how you would create the new workbook.

• Hi,

The simplest solution is to filter and then copy/paste. If this is not the solution you are looking for, then please share your requirement.

• Sean says:

Just saying that this was really helpful and after a little bit of tinkering it’s doing exactly what I need without fail.

Understanding the sytax takes a few minutes but it’s really quite simple and cleverly put together. Kudos.

• Denise says:

I am trying to create a lookup to match mulitple values for duplicates from two workbooks that have multiple columns but am having trouble. I tried the array, but can’t seem to get the correct values or the value only populates in the first couple rows them I only get blanks for the remainders.

• Hi,

• Denise says:

HI Ashish

I am new to this online blog response thing. This is just a example of the data I am trying to join. I couldn’t send the real data because it has confidential information. Please let me know if you can help.

http://1drv.ms/1HLFxmi

Forgot to leave my formula, Thanks

=IFERROR(INDEX(‘[dup test file 11.12.15.xlsx]Test’!\$E\$2:\$E\$16,SMALL(IF(‘[dup test file 11.12.15.xlsx]Test’!\$D\$2:\$D\$16=\$C2,ROW(‘[dup test file 11.12.15.xlsx]Test’!\$E\$2:\$E\$16)),ROWS(\$G1:G1))),””)

Thanks
Denise

• Denise says:

Hi Ashish

I actually figured it out on my own. Thank you for having so much information available to aid me.

• You are welcome. Glad you could figure out the solution yourself.

• Denise says:

I am trying to bring the values from column E in the dup test file to column F or G in the Data test file. The value I was trying to use as a lookup was column C in the data test file with column D in the dup test file .

• Denise says:

I am trying to bring the values from column E in the dup test file to column F or G in the Data test file. The value I was trying to use as a lookup was column C in the data test file with column D in the dup test file .

Hi,

Its a great solution if the data is in the column format. Do u have any solution if the data is the row format. Please help me with solution

• Anoop says:

Hi Ashish,

I have a big data of one month transactions which includes date invoice no and amount. Another sheet in which i have transactions converted to installments which has only date and amount for that particular month. So i want to v look up and get the invoice no in the second sheet as there is so many duplicate values in both sheets. Please reply ASAP.

• Satanjaya Singh Pundeer says:

hi,
I have daily sales report for items on sheet1 and inventory on sheet 2. The sales happen daily and are entered in descending rows. There r 3 columns in sheet 1 – sale, return and counter stock delivery. On sheet 2, is fixed list inventory. I am trying to add formula in sheet 2, where it takes item number and goes to sheet 1, search it in the item number column there and adds values in the corresponding rows. The trick is same item can appear number of times. so add all of them but show number in the same cell in sheet 2. Means it will keep changing as sales or return or stock delivery happens. Is it possible? Please help.

• Hi,

One can explore the SUMIF() function to solve the problem. But the problem is that you want the value in the same cell to keep changing. That would require a VBA solution.

Hi Ashish,

I had a question and would be so grateful if you could spare a couple of minutes.

So I have a testing sheet ‘4-6-2016’ where I am using vlookup (along with iferror and concatenate functions) to pull corresponding information from sheet, ‘data15’

I concatenated employee id number with each training name to name a unique column.

The problem is, the id and training name make a good unique identifier but if someone has done the training multiple times, it chooses the first value it finds (I think).

Is there a way to get the smaller value i.e: the older date. I saw some posts in your comment section and min or small section might work but I don’t know how to incorporate it within my formula.

Is there any way to add the small/min condition within this formula to obtain the smaller value from column 4?

• Hi,

Ashish I think I may have solved my own problem.

Basically my vlookup was pulling information from a table where I had multiple matches (apart from the column being looked up). I wanted to pick the smaller value everytime this occurred, so I sorted in ascending order, so smallest dates would get selected everytime.

Thank you for your willingness to help out!

• Hi,

Glad to hear that you have solved the problem by yourself.

• Tricia-Anne Morris says:

Hello Ashish I am trying to calculate the number of times a student was late using the condition

“for every 4 times late in a term (i.e. 3 months) the student loses a point from final grade”

I need a code that says: For every time count of L (for specified range) = 4 then score = 1 + each additional instance L=4.

This is an example of what the first term looks like, where P= Present/not late and L= Present/late

Last Name Morris
First Name Tricia
2/1/2016 L
2/4/2016 L
2/8/2016 L
2/11/2016 P
2/15/2016 P
2/18/2016 P
2/22/2016 P
2/25/2016 L
2/29/2016 L
3/3/2016 L
3/7/2016 P
3/10/2016 P
3/14/2016 P
3/17/2016 L
3/19/2016 L
3/21/2016 P
3/24/2016 L
3/28/2016 L
3/31/2016 P
4/4/2016 P
4/7/2016 P
4/11/2016 P
4/14/2016 P
4/16/2016 P
4/18/2016 P
4/21/2016 P
4/25/2016 P
4/28/2016 P
4/30/2016 L

• Tricia-Anne Morris says:

Thank you for any assistance you can give!

• Hi,

The data has not been pasted properly. Please paste the data in a workbook, upload it to some free file hosting service such as Google Drive, OneDrive etc. and then share the download link here.

Please also show the expected result on another tab in that workbook.

• rajee says:

I’ve pivot table report which has duplicate project names. That is the unique name I need to use to make a summary report out of it.

now how do I create a report from it using that multiple duplicate project names and bring the relevant records to my summary report. please advice.

• Sathiya says:

Hi sir,
Start Time in Sheet DG should be in between MF Start and End.

Indus_Site_ID DG Start Time
IN-1095006 9/5/2016 0:02
IN-1137882 9/5/2016 0:08
IN-1077456 9/5/2016 3:15
IN-1095006 9/5/2016 9:14
IN-1095006 9/5/2016 10:03
IN-1095006 9/5/2016 12:01
IN-1248315 9/5/2016 17:05
IN-1094855 9/5/2016 17:53
IN-1095006 9/5/2016 19:54
IN-1077456 9/5/2016 20:00
IN-1281872 9/5/2016 20:35
IN-1095006 9/5/2016 23:58

Indus_Site_ID Start Time End Time Expected Result
IN-1095006 9/5/2016 0:02 9/5/2016 5:59 9/5/2016 0:02
IN-1137882 9/5/2016 0:08 9/5/2016 5:01 9/5/2016 0:08
IN-1281872 9/5/2016 0:13 9/5/2016 6:10 NA
IN-1248315 9/5/2016 0:15 9/5/2016 3:30 NA
IN-1077456 9/5/2016 0:16 9/5/2016 6:09 9/5/2016 3:15
IN-1094855 9/5/2016 0:17 9/5/2016 18:53 9/5/2016 17:53

• Hi,

Enter this formula in cell D2 of sheet2 and copy down

=IFERROR(INDEX(DG!\$B\$2:\$B\$13,MATCH(1,INDEX((DG!\$A\$2:\$A\$13=Sheet2!A2)*(DG!\$B\$2:\$B\$13>=Sheet2!B2)*(DG!\$B\$2:\$B\$13<=Sheet2!C2),,),0),1),"") Hope this helps.

• varun says:

Hi Ashish,
I am trying multiple criteria lookup using Index, Match function form another worksheet. my index Match array function is working but my file is very slow. can you please advise any alternative function to lookup or match multiple criteria from another worksheet.

cheers
varun

• Hi,

• Hi,

Given the size of the data you are dealing with, the best tool to use here is Power Query (a free Business Intelligence add-in from Microsoft for Excel 2010 and 2013). This feature is art of the Native Excel application in Excel 2016 (available under Data > Get & Transform). I just tried it and got the correct result. So you have Power Query/Excel 2016 installed on your system?

• varun says:

yes, i have power query. can you advise the steps

• Hi,

Here are the steps

1. Created a Query with the name of Data (range of A1:I6 of one table). Just load this as a connection (not as a Table); and
2. Created a Query with the name of Data1 (range of A1:H6 of the other table). Just load this as a connection (not as a Table); and
3. The third table basically merges the tables after joining the tables on two columns (Ref doc number and amount). Here is the M query for it

let
Source = Table.NestedJoin(data1,{“Ref Document Number”, “Value in Obj. Crcy”},data,{“Document Number”, “Amount in local currency”},”NewColumn”,JoinKind.LeftOuter),
#”Expanded NewColumn” = Table.ExpandTableColumn(Source, “NewColumn”, {“Assignment”}, {“NewColumn.Assignment”}),
#”Renamed Columns” = Table.RenameColumns(#”Expanded NewColumn”,{{“NewColumn.Assignment”, “Assignment”}})
in
#”Renamed Columns”

Hope this helps.

• varun says:

hi Ashish,

following steps creating many lines than original data. i cannot figure out why it it creation 10,000 lines when data in only for 800 lines.

. Created a Query with the name of Data (range of A1:I6 of one table). Just load this as a connection (not as a Table); and
2. Created a Query with the name of Data1 (range of A1:H6 of the other table). Just load this as a connection (not as a Table); and
3. The third table basically merges the tables after joining the tables on two columns (Ref doc number and amount). Here is the M query for it

let
Source = Table.NestedJoin(data1,{“Ref Document Number”, “Value in Obj. Crcy”},data,{“Document Number”, “Amount in local currency”},”NewColumn”,JoinKind.LeftOuter),
#”Expanded NewColumn” = Table.ExpandTableColumn(Source, “NewColumn”, {“Assignment”}, {“NewColumn.Assignment”}),
#”Renamed Columns” = Table.RenameColumns(#”Expanded NewColumn”,{{“NewColumn.Assignment”, “Assignment”}})
in
#”Renamed Columns”

Thanks
VArun

• Hi,

I do not know what you want to do. Please describe your problem in detail. Show your input data and your expected result.

• varun says:

hi Ashish,
https://1drv.ms/f/s!AuwmMzFnFJDccHW39EVXG3rskeA

I am trying to look up Amount and Ref doc no to get assignment field using a power query. when i merge data from two table to get assignment field it creates many lines.

Thanks
varun

• Hi,

That is happening because for many rows on the GL data worksheet, a combination of Ref Doc Number and Amount is available more than once on the Assignment Data worksheet. You will understand what I mean when you write the following formula in cell G2 of the GL data worksheet and copy down

=COUNTIFS(‘Assignment Data’!\$G\$2:\$G\$1738,’GL DATA’!C2,’Assignment Data’!\$E\$2:\$E\$1738,’GL DATA’!F2)

• Sonny says:

Hi there,
I’m trying to work on a Workbook and falling short of a solution.
I was wondering if you maybe able to give me some guidance.
What I have is 2 worksheets Sheet1(Data) and Positions.(Results)

(WS)Sheet1:
E1:DD1 Has names of training courses
A3:A111 Has job position titles
E3:DD111 is populated with YES if the job position title requires a particular training course.

(WS)Position:
I have a dropdown list of sheet1 A3:A111
Depending on what Job selected from drop down list I would like to search along the row of sheet1 of that job position title for a “YES” if found copy training name course and populate down from Position A12 A13 A14 etc
I need this to be dynamically done as the YES’s in sheet1 could be changed.
I can manually enter if’s but this just makes it static for one job position selected.
I hope I have explained this so that you can understand what I am after?
Regards

• Sonny says:

The Dropdown list is linked to Position D2

• Hi,

Try this

1. The validation in cell D1 of the Position worksheet should be built from range A3:A111 of sheet1
2. After selecting an entry in cell D1 of the Position sheet, enter this array formula (Ctrl+Shift+Enter) in cell A12 and copy down

Hope this helps.

• SonnyMRA says:

Hey there thanks for that…but I still get an error

• Why is there is a #VALUE! error coming in your formula. Copy and paste my formula as it is.

• SonnyMRA says:

has removed the error but resulting in 0’s being displayed

• Hi,

In cell A11, enter this array formula (Ctrl+Shift+Enter) and copy down

Hope this helps.

• Sonny says:

🙁

Nothing happens when I press ctrl shift enter

Also nothing is happening in cell A11 … so I took out the error check and i’m still getting the same error as above. The error shows when I evaluate. When I get to the last evaluation….. I step in and it say’s the cell currently being evaluated contains a constant

• Hi,

It is working perfectly for me. I really cannot tell what mistake you are committing.

• Sonny says:

Hi there are you not able to edit the WB in one drive to have it working so that I may see it?

• No, It requires me to sign in – which I do not want to.

• Josh says:

Hi Ashish – thanks for this informative article. It looks like the explanation of your array article at the top is broken. I want to do something similar to this but can’t figure out how to manipulate the formula.

At the top of the sheet, I want to have a cell with a drop down of cars.

Once a car is selected, I want to populate a list of all parts included in that specific car.

I have a vlookup of car to car parts. The number of car parts to cars can change dynamically.

Can you paste in your array formula letting me know which parts of the formula should point to which part of my query? Appreciate the help

• Hi,

• Ashok Jangra says:

Hi Ashish Sir,

Here I attached here a sheet in which we have Glasses model name with length, width, thickness and Category (Column A to E). Suppose one customer asked me to supply “TX4-G-AR” (J2) model but that model I don’t have in stock so to fulfil customer’s requirement I have to cut just next bigger Model in terms of length and width of “TX4-G-AR” to supply it as “TX4-G-AR” but category and thickness should be the same . So I manually selected those models and filled in K2:AN2, But my requirement is when I write any model in J2 then next 30 bigger models should come automatically in K2:AN2

• Hi,

I cannot download the workbook. That takes me to a page which requests for credentials.

• Ashok Jangra says:

Hello ,

I have changed settings, hope you can excess it now.

• Hi,

Try this

1. Enter this array formula (Ctrl+Shift+Enter) in cell K2 and copy to the right

=IFERROR(INDEX(\$A\$2:\$A\$410,SMALL(IF(\$B\$2:\$B\$710>=\$J3,IF(\$C\$2:\$C\$710>=\$J4,IF(\$D\$2:\$D\$710=\$J5,IF(\$E\$2:\$E\$710=\$J6,ROW(\$B\$2:\$B\$710)-ROW(\$B\$1))))),K\$1),1),””)

2. In cell K3, enter this formula and copy down/right

=VLOOKUP(K\$2,\$A\$2:\$E\$710,MATCH(\$I3,\$A\$1:\$E\$1,0),0)

Hope this helps.

• Ashok Jangra says:

Hello Sir,

Thanks for your revert , This formula is helping to get next 30 bigger models but model in K2 should be nearest/bigger match of J2 in terms of lenth and width so that if we cut that model to the size of J2 our wastes will be minimum. accordingly L2 should be 2nd nearest bigger match and so on….You can refer my manually written models in the sheet.

thanks

• Hi,

Type Area in cell F1. In cell F2, enter this formula and copy down

=B2*C2

Now select A1:F710 and sort the Area column (column F) in ascending order. The formula will recompute and our results will match perfectly.

Hope this helps.

• Ashok Jangra says:

yes Sir,

It works. thanks .

But unable to understand why you select only upto A410 below why not A710..

=IFERROR(INDEX(\$A\$2:\$A\$410

• You are welcome. You may edit the formula to select till there. Remember to press Ctrl+Shift+Enter.

• Hi,

The solution which I shared with you here is a semi dynamic solution in as much as it involved creating a spare column and then sorting it. If you want a dynamic solution, then you may refer to this post.

Hope this helps.

• Josh says:

I have been struggling with this for a few days – I am trying to populate column C.

I want to do a look up based on two criteria:

What ID is it?
Does the date fall between a certain date range?

The end result will be pulling the correct company and populating column C.

Caveat here is that I am running into some size issues – this specific lookup would be on 30K rows in addition to the approx 90MB file I already have.

I imagine it has to do with array and a few nested formulas but any help would be amazing!

Any help would be much appreciated!

• Hi,

In cell C2, enter this formula and copy down

=INDEX(\$J\$2:\$J\$5,MATCH(1,INDEX((\$G\$2:\$G\$5=B2)*(\$H\$2:\$H\$5<=A2)*(\$I\$2:\$I\$5>=A2),,),0),1)

Hope this helps.

• Josh says:

Thanks Ashish – this is super helpful! Do you know of any way to do this that doesn’t use index match. The actual workbook I am using has 30k rows so I have to run the update overnight.

• The only other way I can try is Power Query a.k.a Get & Transform in Excel 2016. Upload the actual workbook to OneDrive and share the download link here.

• RS says:

Hi,

I have item number in column A, in column B – picture for this number. But some of the items have 4 pictures, and so there are rows with the same number but different picture. This table should look like: in column A – item number and in column B, C, D etc. to have the pictures. So do you have any idea to do it?
Some friends gave me a formula which contains “MCONCAT” but this function doesn’t work with excel 2016.
the formula is:
=MCONCAT(OFFSET(\$B\$1,MATCH(C2,\$A\$2:\$A\$8236,0),,COUNTIF(\$A\$2:\$A\$8236,C2)),”,”)

So please give me solution that doesn’t contain this formula “mconcat”

• Hi,

In cell B2 of “how i want to look like” worksheet, enter this array formula (Ctrl+Shift+Enter) and copy down/right

=IFERROR(INDEX(‘how i recieved info’!\$A\$2:\$B\$10,SMALL(IF(‘how i recieved info’!\$A\$2:\$A\$10=’how i want to look like’!\$A2,ROW(‘how i recieved info’!\$A\$2:\$A\$10)-ROW(‘how i recieved info’!\$A\$1)),COUNTA(‘how i want to look like’!\$B\$1:B\$1)),2),””)

Hope this helps.

• Patrea says:

I want to fill column e white if column j is a x?