Index / MAtch + go to next

[xatomicx]

Hi everyone,

The index match formula mechanic is just something i don't get, no matter what i tried i don't quite grasp it...

Here is what i need.

Formula located in B17

needs to match 2 criteria
criteria to search is in B4, and second in B5

Criteria B4 search array is on sheet "dumpfix" Column A:A
Criteria B5 search array is on sheet "dumpfix" Column D:D

If a match is found, i want to return the value in "dumpfix" F:F in my B17 formula.

So far so good.
There are multiple occurence... so in B17, i want occurence 1 ... then i need the formula to continue searching, and input next result in B18 ... and so on until all of them are found.

Anyone can help?

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[xatomicx]

I'll see what i can do, it is very sensible data, and there are over 10k rows, so i dont know how i can "falsify" the info and be truly representative at same time. Can we just give it a try without?.. If it doesnt work, i'll build something from scratch and change all the data.

[Xandler]

Although, I can attempt to explain what it does...I fear I would not be able to. However, I think this website explains it pretty thoroughly.

http://www.mbaexcel.com/excel/how-to...x-match-match/

If that violates any forum rules (I'm unsure if it does), I'll remove or a mod can delete/edit this post.

[JohnTopley]

Try this in B17

=IFERROR(INDEX(Dumpfix!$F$2:$F$100,SMALL(IF((Dumpfix!$A$2:$A$100=$B$4)*(Dumpfix!$D$2:$D$100=$B$5),ROW(Dumpfix!$F$2:$F$100)-ROW($F$2)+1,""),ROWS($F$2:F2))),"")



...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy down


You may need to change ranges to suit your data (above assumes data starts in row 2)

[xatomicx]

Thanks john !
That works A1 !!!

Can i crank the difficulty up?
Now that i have my result in B17 + ... in C17 +, i need the exact same Formula, and add a criteria which is my result of B17, and if it matches all the 3 (the 2 existing plus the previous results of your formula), to return dumpfix E:E ... I dont think i need to +1 the row as it can triple verify b4,b5 and now b17, and the result is unique.

Basically, the first critera is a name
The second is a date
The result is a status

So now that i got the status (thanks to you) i want the name, the date, and the status, and in E:E is the time which i want as a result

[JohnTopley]

Don't understand your requirement re C17.

i need the exact same Formula, and add a criteria which is my result of B17, and if it matches all the 3 (the 2 existing plus the previous results of your formula), to return dumpfix E:E

with the existing criteria this will returnn column E

=IFERROR(INDEX(Dumpfix!$E$2:$E$100,SMALL(IF((Dumpfix!$A$2:$A$100=$B$4)*(Dumpfix!$D$2:$D$100=$B$5),ROW(Dumpfix!$F$2:$F$100)-ROW($F$2)+1,""),ROWS($F$2:F2))),"")

What is the additional criteria and what is it matching?

[xatomicx]

The additional criteria is in B17, and the array is in dumpfix e:e

And we can remove the +row, as criteria 1-2-3 will bring a unique value, and we dont need to scroll them down.

The formula will be place in C17

[JohnTopley]

still confused: if we are comparing b17 with E:E, what range are we returning ?

=IFERROR(INDEX(Dumpfix!??????,SMALL(IF((Dumpfix!$A$2:$A$100=$B$4)*(Dumpfix!$D$2:$D$100=$B$5)*(Dumpfix!$E$2:$E$100=$B$17),ROW(Dumpfix!$F$2:$F$100)-ROW($F$2)+1,""),ROWS($F$2:F2))),"")

[xatomicx]

Ill go from scratch

Yeah sorry, i'm confused myself.. lets just go from scratch.

Criteria that need to match:
B4, array to search "dumpfix" A:A
B5, array to search "dumpfix" D:D
B17, array to search "dumpfix" B:B

If all the criteria match return value in E:E

The formula is in C17

I dont need to go to the next row and do the checkup again, as its a unique value, not multiple.

[JohnTopley]

Try

=IFERROR(INDEX(Dumpfix!e2:e100,SMALL(IF((Dumpfix!$A$2:$A$100=$B$4)*(Dumpfix!$D$2:$D$100=$B$5)*(Dumpfix!$B$2:$B$100=$B$17),ROW(Dumpfix!$F$2:$F$100)-ROW($F$2)+1,""),ROWS($F$2:F2))),"")

OR

try

=IFERROR(INDEX(Dumpfix!e2:e100,MATCH($B$17,Dumpfix!$B$2:$B$100,0))