Combining multiple index match in one formula.

[Nickel17]

Good afternoon,
I have searched the forums and was unable to find what I was looking for. Perhaps I wasn't using the proper search verbiage, if this has already been posted, I apologize.

I have written 2 Index|Match formulas which are for separate employee locations. The reason there are two is that I cannot figure out how to make this into one formula and I'm getting an N/A which is driving me nuts. I'm new to using index match but I like it and have replaced all of my vlookup formulas. It just looks a lot more "clean" to me. Here are my formulas and if you are able to solve this issue, can you please explain it to me so that I can learn? Thank you in advance for any assistance you may be able to provide.

=IF(ISBLANK(B2),"",INDEX(Location1,MATCH(B2,CP_Employees,0)))

=IF(ISBLANK(B2),"",INDEX(Location2,MATCH(B2,HQ_Employees,0)))

 

[Nickel17]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Requestor[/TD]
[TD]Practice Area[/TD]
[TD]Matter#[/TD]
[TD]Matter Name[/TD]
[TD]Type[/TD]
[TD]New or Existing[/TD]
[TD]D.O.C.[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SMA[/TD]
[TD]Lit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AV[/TD]
[TD]Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]


My sheet looks like this.

 

[Oeldere]

=IF(ISBLANK(B2),"",INDEX(Location2,MATCH(B2,HQ_Employees,0)))

Probably the formula is =IF(ISBLANK(B3),"",INDEX(Location2,MATCH(B3,HQ_Employees,0)))

AV (=b3) does not exist in the list HQ_Employees

 

[FDibbins]

It's hard to make out your ranges when you use range names like that

Maybe something like this though?
=IF(B2="","",iferror(INDEX(Location1,MATCH(B2,CP_Employees,0),INDEX(Location2,MATCH(B2,HQ_Employees,0))

What determines which table to use?

 

[Nickel17]

Hi.

Both formulas work but not in the same column so I have added a separate column, in a test sheet, for HQ employees. So, if I place the HQ formula in the "HQ" column, I get a correct return value, but an #N/A in the CP Column. The #N/A is driving me nuts and ideally, I would like to just keep my one location column but have my formula check both locations and then determine where the employee works.
I should mention that the "database"(really an extra table at the end of my workbook) has 4 extra columns with defined names CP_Employees, Location1 (which = CP), HQ_Employees, Location2 (which = HQ). These columns are I:L if it helps.

 

[FDibbins]

Just to work with here, remove the range names and just post the actual formulas.

Did you see this question from me?

What determines which table to use?

 

[Nickel17]

Just to work with here, remove the range names and just post the actual formulas.

=IF(ISBLANK(B2),"",INDEX(LawDb1!J:J,MATCH(B2,LawDb1!I:I,0)))

< only returns CP

=IF(ISBLANK(B2),"",INDEX(LawDb1!L:L,MATCH(B2,LawDb1!K:K,0)))

< only returns HQ


Did you see this question from me?

The Employee initials in Column B in the active worksheet is what determines the location. I am running reports for 2 sites where the client would like to see how much work is being processed at each site. So my co-worker at the other site would enter the work being done at HQ while I enter what is being done at CP.

 

[Nickel17]

Imgur: The most awesome images on the Internet
Here is a link to two images of my workbook.

=IF(ISBLANK(B2),"",INDEX(LawDb1!J:J,MATCH(B2,LawDb1!I:I,0)))

< only returns CP

=IF(ISBLANK(B2),"",INDEX(LawDb1!L:L,MATCH(B2,LawDb1!K:K,0)))

< only returns HQ

The initials in (B2) in the active worksheet will determine the location.

 

[FDibbins]

I am unable (not allowed) to go to file-hosting sites

 

[Nickel17]

I am unable (not allowed) to go to file-hosting sites

Makes sense. Sorry about that.
So in my LawDb1 sheet, I have added the following columns I (which is where the initials for the CP employees is located), J (CP location), K (HQ Employees list), and L (HQ location). Now that I think of it, do I even need columns J and L? Couldn't I write something like (J:J="CP") and (K:K="HQ)? Or is it better to have the separate columns? Essentially, I just want to do a lookup where it checks the data in B:B in the active sheet against the locations of the employees and populates either "CP" or "HQ" in Column I of the active sheet. Am I overcomplicating things?