Hi All,
I'm looking for some assistance with a problem I'm having that involves lookups. I would prefer to use the INDEX and MATCH functions if possible.
I need a formula solution instead of VBA. I feel like this is an easy one for someone of my excel aptitude but not sure why I'm having such a hard time with it.
I've attached a copy of the excel file (note that it is not the full excel file. I extracted enough information just to make the situation and need clear).
I need to place a formula in cell B2 that I can copy down that will look up the "department value" in the table on the right and return the number of tasks for that department according to the specified gateway in cell A2. Can't use VBA. If there is an easy way to use INDEX and MATCH I would prefer that.
I tried the below formula but there were too many arguments for the function and it wouldn't work. (Note that the actual file I'm using has the lookup table on a different sheet (Lookup_Tables) than the sheet I need the formula in (CDB_Download).
IF(C2="KMP",VLOOKUP(A2,Lookup_Tables!E4:E17,2),IF(C2="KPx",VLOOKUP(A2,Lookup_Tables!E4:E17,3)),IF(C2="KVx",VLOOKUP(A2,Lookup_Tables!E4:E17,4)),IF(C2="KEx",VLOOKUP(A2,Lookup_Tables!E4:E17,5)),IF(C2="KQx",VLOOKUP(A2,Lookup_Tables!E4:E17,6)))
Bookmarks