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.

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)

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.

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,

Please share data and your question - describe your question. Also, share your expected solution. Upload your workbook to OneDrive and share the link of the workbook here.

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.

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.

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.

Many thanks for your help

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.

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.

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),"")

Hi,

Thks a lot.... Now its coming. Can you pls explain the formula. Also let me know the difference between Ctrl + Enter & simple Enter in formulae.

You are welcome. Glad to help. It is not Ctrl+enter but Ctrl+shift+Enter. These are called array formulas. You may refer to the following link on the Microsoft website to get a basic understanding of array formulas

http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

To get a better understanding of a similar formula, refer to my article at the following link

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

Thks Master ji!!!!!!!!!!!!!!!

You are welcome.

How do I alter this formula to display the range of values found across in columns instead of down rows?

Hi,

Upload your file to SkyDrive and share the link of the uploaded file here.

This is exactly the solution I've been searching for. Thank you for posting your expertise!!!

You are most welcome.

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.

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.

Please check and revert.

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?

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.

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.

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.

Thank you. I am based in India.

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.

It worked!! Thanks again

You are welcome. Glad to help.

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.

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.

The Linked file http://sdrv.ms/Ra14Le has

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.

Hi,

Thank you very much

You are welcome. Thank you for the feedback.

Look up one value in a range and return multiple corresponding values

Lookup value is in k2 and range is A2:A23

If k2 = A2:A23 I would like to find out multiple values in the range G2:G23 in column L (result mulitple occurance would be Vertically or Horizontally) by using Ctrl + shift + enter

Also posted in the below forum

http://www.mrexcel.com/forum/excel-questions/679327-look-up-one-value-range-return-multiple-corresponding-values.html

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),"")

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.

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.

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.

Completed!

Thank you sir for your tremendous Excel help..

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

You are welcome.

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.

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.

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?

Thanks in advance

Hi,

Upload your file to SkyDrive and share the link of the uploaded file here.

Here you go

http://sdrv.ms/1370bKG

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.

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)

I have uploaded the filehttp://sdrv.ms/10c1MvF

Thanks again for all your help, much appreciated

Hi,

Instead of having multiple columns of Part options, you should fist get all part options columns into one column. To do this, you may read the procedure here - http://www.ashishmathur.com/converting-a-matrix-data-layout-to-a-tabular-layout/.

Hope this helps.

Hi Ashish,

can you please help me with this?

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,

I cannot understand your question. Please upload your file to SkyDrive and share the link of the uploaded file here.

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")

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

Hi,

You may refer to my solution in this workbook.

Hope this helps.

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.

Upload your file where you put my formula

https://skydrive.live.com/?cid=690b71b5f6609381

see the data table- trying to create a report with format shown on reults tab.

Thank you in advance

There is no file there.

sorry - forgot to make it public:

https://skydrive.live.com/#cid=690B71B5F6609381&id=690B71B5F6609381%21105

Hi,

You may refer to my solution in this workbook.

Hope this helps.

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.

You are welcome.

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.

File Link:

http://sdrv.ms/13mxSXT

Hi,

You may refer to my solution in this workbook.

Hope this helps.

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?

Thanks in advance for your help.

Jack Sons.

Hi,

Try this

1. Download and install the morefunc add-in from the following link on my website - http://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.

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.

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 ?

Thanks in advance.

Regards,

Rajender

Hi,

You may refer to my solution in the this workbook.

Hope this helps.

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 ?

Thanks in advance for your assistance.

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.

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

Menu Name : Chicken Biriyani

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

Item Menu Master

Menu Code Menu Name Item(s) Measured For Qty Unit Rate

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

Could you please help me out in this listing.

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

Thanks

Richard

Hi,

Upload your file to SkyDrive/Google Docs and share the link of the uploaded file here - please do not mail the file to me.

Hi,

You may refer to my solution in this workbook

Hope this helps.

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.

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

3 C. Pepper Adams x

4 C. Sanders Adams

5 D. Sanders Adams

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,

Your data is not clear. Upload your file to SkyDrive and share the link of the uploaded file here.

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.

Please note that to use this solution, you will have to download and install the Morefunc add-in from here.

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

Hope this helps.

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,

I cannot understand your question. Share sample data, the business question and the expected solution. Upload your file to SkyDrive and share the link of the uploaded file here.

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.

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.

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.

You are welcome.

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

25-01-14 B10 Badan 150

28-01-14 A10 Abilash 150

02-02-14 C10 Chandu 120

15-02-14 B10 Badan 180

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.

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.

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.

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,

I will be able to help you if you share the file, explain the business problem and show me the result that you are expecting. Please share a link from where I can download the file.

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?

Thanks a million in advance

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,

https://onedrive.live.com/redir?resid=47B9BBE3EB3B5824!107&authkey=!ADoGGWK_zCGySkg&ithint=file%2cxlsx

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.

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.

Thanks again, and as usual you are truly amazing

Hi,

You are welcome. Thank you for the feedback.

Dear Sir,

For same example, i want between two dates lookup.

Could you help.?

Hi,

Please explain the problem clearly. Also, share the link from where I can download the workbook.

Dear Sir,

Here with I'm providing the link of the workbook for your ref. https://drive.google.com/file/d/0Bx096BifdF1xNnNQdkJBN0t0Nnc/edit?usp=sharing

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.

expecting your earliest reply.

Thanks in advance.

Sunil Kumar .N

Hi,

You may refer to my solution in this workbook.

Hope this helps.

Thank you Sir, its working great help by you.

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.

If you identify any lapses, then please mark them in yellow and share your expected answer.

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.

Hi,

You may refer to my solution in this workbook

Hope this helps.

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%

Hi,

Thank you for your kind words. I am glad you liked the content on my website. You have posted the question in the wrong Blog article. Anyways, my solution to this problem would be to first append fata from all the Shift worksheets to one worksheet and then filter the data to get all records which have a %age greater than 95%. To append data from multiple worksheets of a workbook, refer to my article at this link - http://www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

Hope this helps.

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).

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.

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!

You are welcome.

Hello Ashish,

I tried using your formula below, but the result does not show in the cell. Please help.

=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.

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.

Hello Ashish,

Please see the following workbook for your reference.

https://onedrive.live.com/redir?resid=ACA5FEF014FD6400!106&authkey=!AHWDQBxI22eAjIE&ithint=file%2cxlsx

I need to be able to search for certain criteria in column A and pull all the data from column B and C.

For example,

If I type 35 and there are 9 items that correspond with that number in column A, then I need all the data from Column B and C for those lines.

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.

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))

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.

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.

Please see example below;

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.

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 Ashish,

Please see the link below.

https://onedrive.live.com/redir?resid=662E642D16E6FD84!117&authkey=!ADAalSfSIM4x8UA&ithint=file%2cxlsx

I haven't included all 11805 account numbers, just a snap shot so you can see what is happening.

Thanks for your help.

Rod

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.

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.

Thanks Ashish.

Do you know of any way around it?

Thanks,

Rod

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?

Thanks for your time,

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.

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.

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,

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

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.

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.

You are welcome. Thank you for your kind words.

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,

I cannot understand your question. Upload your workbook to OneDrive and share the download link here.

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.

My OneDrive link is below:

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

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.

Here is the link

https://onedrive.live.com/redir?resid=5FA80092A6FD41DF!134&authkey=!AJAWjso_qz_aKWA&ithint=folder%2c

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 .

Here is the link

http://1drv.ms/1HLFxmi

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

Hi,

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

https://onedrive.live.com/redir?resid=3A3C24152069AF9B!107&authkey=!ADTZzsIBEqlxM9U&ithint=file%2cxlsx

Please reply ASAP

Hi,

You may refer to my solution at this link.

Hope this helps.

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.

Hi,

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

https://onedrive.live.com/edit.aspx?cid=b9c42e1973df4f63&page=view&resid=B9C42E1973DF4F63!225&parId=B9C42E1973DF4F63!109&app=Excel

Please find the link and reply ASAP

Hi,

There is no file at that link.

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.

my formula is: =IF($K12<'4-6-2016'!$R$4,"n/a",IFERROR(VLOOKUP(CONCATENATE($A12,L$9),Data16!$O:$U,4,FALSE),"Not found- Please Review"))

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

Thank you in advance!

Hi,

Upload the workbook to OneDrive and share the download link here. Also, show your expected result in that workbook.

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.

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

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.

could you help please?

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.

I cannot understand your question.

Hi sir,

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

please help me with Lookup function with multiple criteria else other option.

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.

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,

Upload the workbook to OneDrive and share the download link here. Please also clearly show the expected result for a few line items.

Hi Ashish,

below link for the workbook

https://1drv.ms/x/s!AuwmMzFnFJDcgSEXi6xXHghzwoVy

thanks

varun

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?

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.

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.

hi Ashish,

Below link for workbook

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)

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

The Dropdown list is linked to Position D2

Here is the Workbook https://1drv.ms/x/s!AmRZISkL4CyPgwEJjNvVPdDGkLSB

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

=IFERROR(INDEX(Sheet1!$E$1:$DD$1,1,SMALL(IF(INDIRECT("'sheet1'!"&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),5)&":"&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),108))="Yes",COLUMN(Sheet1!$E$1:$DD$1)-COLUMN(Sheet1!$D$1)),COUNTA(Position!A$11:A11))),"")

Hope this helps.

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

=INDEX(Sheet1!$E$1:$DD$1,1,SMALL(IF(#VALUE!("'sheet1'!"&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),5)&":"&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),108))="YES",COLUMN(Sheet1!$E$1:$DD$1)-COLUMN(Sheet1!$D$1)),COUNTA(Position!A$11:A11)))

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

=INDEX(Sheet!$F$1:$DD$1,1,SMALL(IF(INDIRECT("'Sheet1'!"&ADDRESS(MATCH(D$2,Sheet1!$A$2:$A$108,0),6)&":"&ADDRESS(MATCH(D$2,Sheet1!$A$2:$A$108,0),108))="YES",COLUMN(Sheet1!$F$1:$DD$1)-COLUMN(Sheet1!$E$1)),COUNTA(Reports!A$11:A11)))

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

https://1drv.ms/x/s!AmRZISkL4CyPgw8C1_mB3iwUfoVF

Is a copy of the lastest Workbook

Hi,

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

=IFERROR(INDEX(Sheet1!$E$1:$DD$1,1,SMALL(IF(INDIRECT("'sheet1'!"&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),5)&":"&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),108))="Yes",COLUMN(Sheet1!$E$1:$DD$1)-COLUMN(Sheet1!$D$1)),COUNTA(A$9:A10))),"")

Hope this helps.

🙁

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.

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.

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,

Share the link from where I can download your workbook. Also, please also show the expected result in that workbook.

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

https://docs.google.com/spreadsheets/d/1iodBmaz2PWLExcXh15O4Rn_O7nb3PGjmMz-RgG6FOKI/edit#gid=1487928837

Hi,

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

Hello ,

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

Hello ,

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

https://docs.google.com/spreadsheets/d/1iodBmaz2PWLExcXh15O4Rn_O7nb3PGjmMz-RgG6FOKI/edit?ts=5878a94f#gid=1487928837

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.

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.

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.

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!

https://docs.google.com/spreadsheets/d/16vB6eXhMbDnSy1C-N4lxgVCLXiYykApOZPiwYc-5KBs/edit?usp=sharing

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.

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.

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,

I do not know how to combine pictures.

Ok. Let´s say that there are no pictures. I share an example with you, because obviously I can´t explain clear what i need 😀

So in the first sheet is the info the way I received it, and in the second sheet is that how should look like.

Hope you will be able to help me.

https://drive.google.com/file/d/0B9vWC3B_WFRUdEw4cXNRdlVvbVU/view?usp=sharing

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.