Forum Discussion
Similar to Index Match Match, Value 1 is Tool# in Range A from database report. Value 2 & Range
I am unable to make the formula complete. This is for a 1,000 + entry report. I have a scaled down example to attache. Tool Log has employee ID as headers and if TOOL ID is Matched in DB Report, and it matches header name that cell shows "In Report" (DB). Should provide an easy visual across employees to see tools assigned to them. Any help would be greatly appreciated.
In G11 it could be
=IF(ISNA(MATCH($C11&G$10,$C$19:$C$29&$J$19:$J$29,0)),"no","In Report")and drag it to the right and down. If I understood the logic correctly.
8 Replies
I wasn't able to figure out how exactly you've laid out your Log section, but have written just three formulas here in this example that show one way to deliver the result you appear to be looking for. I'll let you adapt them to your layout.
In the example, I've used the UNIQUE function to get a list of the three employee IDs that you provided in your example. That formula is in cell N8. I also used SORT to put them in order.
That list is used to provide a data validation drop down list in cell P8. If you just select a different employee there you'll see the results to the right of it change. That's all you need to do to test what I've created. All the rest is automatic.
In cell Q8 there's a FILTER function that just lists the tool# for tools assigned to the selected employee.
In cell S8, FILTER is used to list not only tool#, but also Job, Loc, Asset, and Type....
Note: UNIQUE, FILTER and SORT all are only available in the most recent version of Excel, so if these aren't working for you when you open it, that would be an indication that you're on an older version of Excel.
Those Dynamic Array functions are amazing in their various applications. I only recently became aware of them...this YouTube video was very helpful in getting a grasp on how they work and how they might be used. https://www.youtube.com/watch?v=9I9DtFOVPIg
I'd encourage you to set aside time to watch it as well.
In G11 it could be
=IF(ISNA(MATCH($C11&G$10,$C$19:$C$29&$J$19:$J$29,0)),"no","In Report")and drag it to the right and down. If I understood the logic correctly.
What IF I wanted more specific information in the "If FALSE" statement of the IF formula?
=IF(ISBLANK($B$3),"",IF(ISNA(MATCH($B3&$S$2,Up2Date!$A$2:$A$20000&Up2Date!$H$2:$H$20000,0)),"no","In Report"))
What would I need to do to get the associated value from the STATUS Column in place of "In Report" text?
...&Up2Date!$H$2:$H$20000,0)),"no","In Report")) change to $H$20000,0)),"no",R2W))
or as may be associated.You may expand it with INDEX/MATCH as
=IF(ISNA(MATCH($C11&G$10,$C$19:$C$29&$J$19:$J$29,0)),"no", INDEX($H$19:$H$29,MATCH($C11&G$10,$C$19:$C$29&$J$19:$J$29,0)) )Result is
SergeiBaklan Thank you so much that accomplished just what I needed. Now I can do a quick view of all tools assigned. I may be interested in pulling the "Status" into the cell instead of placing the text "In Report"... That would give me the true snap shot of employee, ownership of tool, and status. Great Job!
