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

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

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

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.

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

The array formula worked perfectly. Thank you!

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

Great, glad it worked for you

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.

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)

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.

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

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.

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)

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)

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.

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

OK thanks, good to know

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1