Indexing with duplicates issue

**kerui** · 08-27-2012, 09:17 AM

In the file attached I am trying to pull out open and filled positions for HR into department worksheets.

Everything seems to be working except for the Member Operations tab, which is pulling in one of the jobs just fine but the others that are deemed to be in MemberOperations dont seem to want to populate.

The "Category lookups" tab establishes the crosswalks for which category is in which department.

Thanks in advance!

Department Position Status working file.xlsx

**NBVC** · 08-27-2012, 09:28 AM

I see lots of trailing spaces in Column E of OpenPositions sheet, and in Category Lookups sheet. Also you Vlookup in column A of OpenPositions doesn't cover the full range in Category Lookups.

You need some cleanup work first...

**bongaan** · 08-27-2012, 09:33 AM

You have a lot of trailing spaces behind the word MemberOperations, that's why it's broken. Let me explain: go to your OpenPositions sheet, copy column A and paste as values. Then press F2 on each of the cells with the word MemberOperations, you will see that there is a lot of space(s) behind some. It only the one without the spaces that gets pulled into your MemberOperations sheet. The problem is within your "Filled Positions by Department.xls" document from where you do the lookup.

**kerui** · 08-27-2012, 10:27 AM

ok I understand its a little messy, the data in the filled and open sheets is copy pasted for a vendor, but my question is, if ultimately the category vlookup is spitting out the correct department, why isnt the index on that department's page properly populating the position?

I have adjusted the category lookup to run from $A$1:$B$100.

important edit : i have fixed the trailing spaces in the category lookup sheet, but I'd like to be able to work around the trailing spaces in the filled and open sheets so that the intern doesnt have to go in and delete the spaces every month, simply copy paste the values.

**NBVC** · 08-27-2012, 10:34 AM

If I update the formula in A10 of OpenPositions sheet to:

=IFERROR(VLOOKUP(E10,'Category Lookups'!$A$1:$B$100,2,FALSE),"")

and copy down, then in sheet MemberOperations, I get 4 rows of data pulled up in the Open Positions section.

**kerui** · 08-27-2012, 10:43 AM

oh wow I cant believe I missed the formula there being so off, I have no idea how it got linked to another worksheet. as always you guys are great, thanks!!!