# Formula needed - line item of unique item matching criteria

1. ## Formula needed - line item of unique item matching criteria

Formula - line item of unique item matching criteria.xlsx

The attached file has 2 sheets: Formula and Data.
The formula sheet is the one where I need the formula. The yellow cells show the result the formula should return.
The data sheet has 2 columns. The first shows the row number of each item. The second column shows the cost type.
The formula needs to return the row number of the first "Accrual" in the data tab. The next result of the formula needs to show the row number of the second "Accrual". The next result of the formula needs to show the row number of the third "Accrual". etc.
Thanks.  Register To Reply

2. ## Re: Formula needed - line item of unique item matching criteria

Try this...
=INDEX(Data!B:B,MATCH(Formula!A2,Data!A:A,0))
copied down  Register To Reply

3. ## Re: Formula needed - line item of unique item matching criteria

This will return the word "Accrual" so I changed the INDEX(Data!B:B,... to INDEX(Data!a:a,...
Unfortunately when copied down it returns:
2
3
6
#N/A
#N/A
2
3
6
#N/A
#N/A

I want it to return:
2
3
6  Register To Reply

4. ## Re: Formula needed - line item of unique item matching criteria

So you just want to return what is in Data A2...A3...A4 etc?

Is so, then just use...
=IF(Data!A2="","",Data!A2)

Or maybe Im not undetstanding what you want?How did you pick 2, 3 & 6 on the Formula sheet?

hmm perhaps this ARRAY formula...
=IFERROR(INDEX(Data!\$A\$2:\$A\$6,SMALL(IF(Data!\$B\$2:\$B\$6="Accrual",ROW(Data!\$A\$2:\$A\$6)-1),ROWS(\$A\$1:A1))),"")
...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. Press F2 on that cell and try again.  Register To Reply

5. ## Re: Formula needed - line item of unique item matching criteria

The array formula worked perfectly. Thank you!  Register To Reply

6. ## Re: Formula needed - line item of unique item matching criteria

Great, glad it worked for you   Register To Reply

7. ## Re: Formula needed - line item of unique item matching criteria

testing.xlsm

Can you please look at this attached file and let me know what's wrong with the formula that's causing it to return the incorrect result? The formula in cell B20 should be returning 6 not 10. Thanks.  Register To Reply

8. ## Re: Formula needed - line item of unique item matching criteria

=IFERROR(INDEX(\$B\$6:\$B\$18,SMALL(IF(\$E\$6:\$E\$18="Accrual",ROW(\$B\$6:\$B\$18)-5),ROWS(\$B\$19:B19))),"")

Your data starts on row 6, not row row 2, as in your 1st sample, you need to "remove" the rows above where your sample data starts

(Again, CSE)  Register To Reply

9. ## Re: Formula needed - line item of unique item matching criteria

So you're saying that I can't have my data start where this new file starts? If not, do you have another solution as my file has headers and subtotals above the data so I need to have my formula allow that.  Register To Reply

10. ## Re: Formula needed - line item of unique item matching criteria

No, thats not what Im saying In your 1st sample WB, your headings were in row 1 and your data started in row 2, so my formula too that into account by using -1...
=IFERROR(INDEX(Data!\$A\$2:\$A\$6,SMALL(IF(Data!\$B\$2:\$B\$6="Accrual",ROW(Data!\$A\$2:\$A\$6)-1),ROWS(\$A\$1:A1))),"")

In your 2nd sample, your data started in row 6, so I adjusted the formula to cater for that by using -5 instead...
=IFERROR(INDEX(\$B\$6:\$B\$18,SMALL(IF(\$E\$6:\$E\$18="Accrual",ROW(\$B\$6:\$B\$18)-5),ROWS(\$B\$19:B19))),"")
(see the bolded parts)

You just need to adjust that value according to what row your data starts in  Register To Reply

11. ## Re: Formula needed - line item of unique item matching criteria

Thank you so much for explaining this to me. I had just taken your formula and used it without understanding the formula.  Register To Reply

12. ## Re: Formula needed - line item of unique item matching criteria

If you index the entire column then you don't have to use an "offset correction".

=IFERROR(INDEX(B:B,SMALL(IF(E\$6:E\$18="Accrual",ROW(E\$6:E\$18)),ROWS(B\$19:B19))),"")

The ROWS() function should refer to the first cell the formula is entered into. If the first cell the formula is being entered into is B19 then use:

ROWS(B\$19:B19)  Register To Reply

13. ## Re: Formula needed - line item of unique item matching criteria

Tony, doesnt referencing entire columns in an array formula, slow things down? (Just asking, for my own benifit)  Register To Reply

14. ## Re: Formula needed - line item of unique item matching criteria

Not in this application.

You're not actually doing anything to the data in column B. You're just "telling" Excel that's where the data is that you want to find.  Register To Reply

15. ## Re: Formula needed - line item of unique item matching criteria

OK thanks, good to know  Register To Reply