Adding and multiple criteria into an Index

[clowesr]

Afternoon Guys

Having a few problems with an Index formula.

I have a load of raw data and I want to pull each of the rows out with a certain criteria,

=INDEX(Installer,SMALL(IF($D$3=over14,ROW(over14)-MIN(ROW(over14))+1,""),ROW(A1)))

This formula pulls of each record that has a "1" in the over 14 array which works perfect, but I want to say:

IF(D3=over14)and(D4=unbilled)

(D3 and D4 are the cells with the criteria in, "over14 is the first array to look in and unbilled is the second array to look in)

is there a way I can put this into the above formula?

if you require an working example let me know

thanks

[ramananhrm]

Please have an addtional 'IF' to your formation.

=INDEX(Installer,SMALL(IF($D$3=over14,IF(d4=unbilled,ROW(over14)-MIN(ROW(over14))+1)),""),ROW(A1)))

or attach sample file to solve it.

[clowesr]

Hi

that just returns an #value error

thanks

[Pepe Le Mokko]

please disregard this post

[clowesr]

they are named ranges

[Pepe Le Mokko]

Perhaps

=INDEX(Installer,SMALL(IF(and($D$3=over14,$d$4=unbilled),ROW(over14)-MIN(ROW(over14))+1,""),ROW(A1)))

committed with Ctrl+Shift+Enter

[clowesr]

Try this for an example sheet

[ramananhrm]

Please paste this function in the cell i6

Array:
=INDEX($C$5:$C$9,SMALL(IF($D$5:$D$9=$I$2,IF($E$5:$E$9=$I$3,ROW($C$5:$C$9)-ROW($C$5)+1)),1))

Non-Array (Ctrl+Shift+Enter is not required)
=INDEX($C$5:$C$9,MATCH(LARGE(INDEX(($D$5:$D$9=$I$2)*($E$5:$E$9=$I$3)*(1/ROW($D$5:$D$9)),0),1),INDEX(($D$5:$D$9=$I$2)*($E$5:$E$9=$I$3)*(1/ROW($D$5:$D$9)),0),0))

[clowesr]

Hi

ramananhrm, thank you for working that out for me :-)

it works perfectly for the first row, but then when I copy the formula down to find the next one with that criteria it doesnt find it and just returns the first one again.

sorry to be a pain

Thanks

[ramananhrm]

Please try this file.

Rgds,

[clowesr]

You beautiful person!! :-) thank you very much for your help

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[clowesr]

I dont see any stars?

[clowesr]

doesnt matter