Index Reference Formula using Search Term

[Jeffy]

Hello everyone,

Brand new to this forum. Thanks in advance for any help you can provide.

In the attached file, I have a "CC Summary" tab (in yellow) that summarizes my "Details" tab (in blue). In the "CC Summary" tab, I have a drop down cell that allows me to cycle through various cost centers.

In "CC Summary," column L, I'm trying to have column L change data based upon the cost center that's selected in A1. And the only data I want to display in column L are line items from "Details" that contain the characters "TBH" or preferably "*TBH" - see column P for the desired result. This would show me any open jobs available for that cost center and leaves out other line item detail not related to headcount.

I've gotten as far as I can on my own with this. My problem right now is that the formula I have is returning all lines. Additionally, once I do get this formula working, ideally, there would be no blank rows - all data relevant to the selection would rise to the first data return in cell L5.

I am intentionally trying to avoid Macros, VB & Pivot Tables to solve this problem.


My poor attempt at his formula is below (and also in the file starting at L5 on "CC Summary":

=IF(ISERROR(SMALL(IF(IF(ISERROR(SEARCH($A$2,DETAILS!B:B)),FALSE,TRUE),ROW(DETAILS!B:B)),ROW(DETAILS!I:I))),"",INDEX(DETAILS!B:I,SMALL(IF(IF(ISERROR(SEARCH(TBH,DETAILS!I:I)),FALSE,TRUE),ROW(DETAILS!B:B)),ROW(DETAILS!I:I)),8))

[FDibbins]

Hi, welcome to the forum

Try this ARRAY formula....
=IFERROR(INDEX(DETAILS!$I$6:$I$154,SMALL(IF((DETAILS!$B$6:$B$154=$A$2)*(MID(DETAILS!$I$6:$I$154,2,3)="TBH"),ROW(DETAILS!$A$6:$A$154)-5),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.

[Jeffy]

Beautiful, thank you!!

[FDibbins]

Happy to help, thanks for the feedback

[Jeffy]

Okay, bringing this thread back up to the top. Over the past several months, this formula has been working great to quickly organize massive amount of data and tell a story. Last week however, the data started to return an untraceable error. I believe the error is related to the "Org" which is the reference criteria.

In the image below, the exact formula is being used in two sections. The section on the left is using the "Cost center" as the reference to pull data. The section on the right is using the "Org" as the reference to pull data. I've highlighted the "Org" sections cell so you can see the formula bar. This formula is the exact same for the "Cost Center" section, with the only change being the reference cell is $J$10 instead of $V$10 and the range for the cost center is $B instead of $A (Org) - the range is on the far right of the image (columns A & B).

Prior to last week, both sections (Cost Center TBH's & Org TBH's) were working perfectly. But now, only Cost Center section is working and Org is not. I've played around with the formula in the Org section to try to isolate where the problem is. I believe the issue might be something in the Org criteria & range. I checked to make sure there are no extra or missing digits (spaces) and I checked to ensure that the cell format for the reference is the same as the cell format in my range.

Note: J10 & V10 are drop down menus and there are multiple cost centers and orgs, so don't be concerned that you are seeing IS700 in V10 and IS300 in the range. This image is only showing a portion of all the data.

Thanks in advance for your help.

Screen Shot 2015-08-10 at 9.54.31 PM.png

[FDibbins]

That is a very small pic, and we cant really work with pics, anyway I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.