INDEX/MATCH yielding multiple results?

**lee4clp** · 08-04-2010, 09:46 AM

Hi everyone,

I have a database consisting of all the jobs we have on hand at work, split into the functions that are needed for each job. You can see the attached pictures to see what I'm talking about. What I need to do is pull rows of information from the main worksheet (named: 'JOBS ON HAND') and copy them to another worksheet within the same file (name: 'Scheduler') IF the row contains a certain word in column E which in this case is 'INKJETTING'.

I have entered this formula in cell A6 of the 'Scheduler' worksheet to pull the FIRST row containing 'INKJETTING' in column E of the 'JOBS ON HAND' worksheet:

=INDEX('JOBS ON HAND'!A:A,MATCH("INKJETTING",'JOBS ON HAND'!$E:$E,0),1)

This works fine, and I have a similar formula in B6 to F6 to pull the rest of the column data from the 'JOBS ON HAND' worksheet (the formula above in A6 pulls only the data from column A in 'JOBS ON HAND' from the row that contains 'INKJETTING').

Hope I haven't lost you all...

My question is this - what formula do I now use in column A7 in the 'Scheduler' worksheet to pull the data from column A in 'JOBS ON HAND' from the SECOND row that contains 'INKJETTING'? There are several rows that contain 'INKJETTING' that need to be pulled.

If I can explain further, please let me know... thanks in advance.

Lee.

P.S. Image1 = 'JOBS ON HAND' and Image2 = 'Scheduler'.

**Whizbang** · 08-04-2010, 10:00 AM

This link should be a good start. It talks about using an array formula to list mutliple lookup results.

http://office.microsoft.com/en-us/ex...001226038.aspx

**lee4clp** · 08-04-2010, 10:56 AM

Thanks for your response!

I did look at the resource and play about with it in my own database but I can only come up with '#NUM!"' I'm afraid. The reference you gave me deals with the VLOOKUP function which I am not using (I'm using INDEX/MATCH to yield my first result because I'm looking-up what's in column E, not the first column which is what VLOOKUP does) and also is used within the same worksheet whereas I am trying to copy the information to a separate worksheet.

I just don't understand enough about it to get somewhere and this can really help me with my work.

**Whizbang** · 08-04-2010, 11:13 AM

Only the first part talks about Vlookup. The artice then uses Index/Match to simulate a vlookup, but to return multiple results. At this point, you are no longer limited to the first column in the table.

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)

1: Change $A$1:$B$7 to whatever your table address is.
2: Change $A$1:$A$7 to whatever range you would like to search (This is not limited to the first column. Pick any range within the table).
3: Change $A$10 to whatever value you's like to search for ("INKJETTING").
3: Change 1:1 to whatver occurence you'd like (i.e if you want the second occurance, change it to 2:2. Copying the formula down the page will do this for you in a sequential way.).
4: Lastly change the 2 at the end to whatever column you want to return the value from.

The key to this formula, and all array formulas, is that you cannot search an enire column (i.e. A:A). You need to have defined ranges. The best way to handle this is to use Dynamic Named Ranges (http://www.ozgrid.com/Excel/DynamicRanges.htm).

**lee4clp** · 08-04-2010, 11:36 AM

OK, I see what you mean... however, it is not working correctly although there are now no errors.

{=INDEX('JOBS ON HAND'!$A$2:$N$300,SMALL(IF('JOBS ON HAND'!$E$2:$E$300="INKJETTING",ROW('JOBS ON HAND'!$E$2:$E$300)),ROW(1:1)),1)}

I followed your instructions (which were very helpful, it made me understand what each part is doing) and came up with the above formula. It did pull something from the correct column but it came from a row in which "INKJETTING" was not in column E so now I'm questioning the mechanics of this. Looking at it, it should be searching 'JOBS ON HAND'!$E$2:$E$300 to find all instances of "INKJETTING" and depending on which instance I want it to show (in this case, the first instance (1:1)) it should show me the data from column A of that row. But it decided to give me the data from column A of the row directly UNDERNEATH my first row that contains "INKJETTING".

Any idea why?

**Whizbang** · 08-04-2010, 11:45 AM

I would suspect that it has something to do with the fact that your tables start on row 2 (ie $A$2).

Try changing all of your ranges to start with row 1 (ie $A$2 to $A$1 and $E$2 to $E$1).

I understand that your first row is your headers and so you thought to not include these in your search. But, as long as your header names are not what you are searching for (ie "INKJETTING" is not the header name), then it should not affect your results to include them in the search. Otherwise you'd probably have to add or subtract 1 after ROW(1:1)) and before the comma (ie {=INDEX('JOBS ON HAND'!$A$2:$N$300,SMALL(IF('JOBS ON HAND'!$E$2:$E$300="INKJETTING",ROW('JOBS ON HAND'!$E$2:$E$300)),ROW(1:1))-1,1)}).

**lee4clp** · 08-04-2010, 12:13 PM

Well that makes a lot of sense, thank you so much for all of your help... it's working 100% and I know just enough myself to manipulate it and tweak it for my needs.

Thanks again! Will mark this as solved if I can.

**NBVC** · 08-04-2010, 12:13 PM

Actually, this should work:

=INDEX('JOBS ON HAND'!$A$2:$N$300,SMALL(IF('JOBS ON HAND'!$E$2:$E$300="INKJETTING",ROW('JOBS ON HAND'!$E$2:$E$300)-ROW('JOBS ON HAND'!$E$2)+1),ROW(1:1)),1)

confirmed with CSE