I have an index formula:
=IFERROR(INDEX([43.xlsx]Who_Did_What!$D2:$D10000,MATCH(1,INDEX(([43.xlsx]Who_Did_What!$C$2:$C$19000=G3)*([43.xlsx]Who_Did_What!$E$2:$E$19000<>"")*([43.xlsx]Who_Did_What!$D$2:$D$19000>0),0),0)),"")
See attached a replica "example 2".
The above formula was in a live workbook which i am trying to fix this problem - i cannot attach this for data protection reasons. However the replica is exactly the same, just replaced the data with fakes.
The formula was in J3,
It is pulling the data from "43.xlsx" which i have attached another replica of.
Currently i understand this formula ignores "-1"s and "0"s, which are in column D, employee number. Issue is now is that there are some employee ID numbers i wish to exclude or for the formula to not return.
These are 896, 872, 873, 874, 875, 876, 877, 878, 879, 880.
I don't know how to include the above ID numbers in the formula.
I am expecting to see GEFF in J3 (formula is looking up "LARGE CAR", G3"). Not MACK as MACK has ID 874.
Hope this makes sense
Bookmarks