Match without Index?

[Nickel17]

Good morning.

I have a spreadsheet which looks like the table below. I have data on a separate worksheet which will add the practice area when I enter in the person's initials. I'd like to populate the Location column using the initials in column B on the active worksheet and for some reason, I am unable to write a formula to get this to work.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]REQUESTOR[/TD]
[TD]Practice Area[/TD]
[TD]Matter #[/TD]
[TD]Matter Name[/TD]
[TD]Type of Filing[/TD]
[TD]New or Existing[/TD]
[TD]DOC[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]4/2[/TD]
[TD]LB[/TD]
[TD]COM[/TD]
[TD]LA2016000123[/TD]
[TD]You v. Me[/TD]
[TD]Correspondence[/TD]
[TD]Existing[/TD]
[TD]4/2[/TD]
[TD]CP[/TD]
[/TR]
[TR]
[TD]4/3[/TD]
[TD]DZ[/TD]
[TD]REG[/TD]
[TD]LA2016000456[/TD]
[TD]TL567[/TD]
[TD]Pleadings[/TD]
[TD]New[/TD]
[TD]4/3[/TD]
[TD]HQ[/TD]
[/TR]
</tbody>[/TABLE]

Here is what I've come up with so far.
This returns "HQ"

=IF(MATCH(B2,LawDb1!$B$16:$B$35,0),LawDb1!$I$3,"")

This returns "CP"

 =IF(MATCH(B2,LawDb1!$B$2:$B$15,0),LawDb1!$I$2,"")

Both return "#N/A" when the statement is false. I'd like to have the cell be blank if there is no data in the requestor column, so IF(ISBLANK( will be used as well.
My question is, how can I combine the two statements to get what I need? Full disclosure I am not well versed with Index:Match, I got it to work once and it took me hours to get it to work correctly.
I appreciate any and all help that you may be able to provide.

Thank you,
Nick

 

[FDibbins]

I prefer to just test for "", so in your formula...
=IF(ISBLANK(D25),"",VLOOKUP(D25,LawDb1[[Matter'#]:[Matter Name]],2,FALSE))
I would just use...
=IF(D25="","",VLOOKUP(D25,LawDb1[[Matter'#]:[Matter Name]],2,FALSE))

 

[Nickel17]

I prefer to just test for "", so in your formula...
=IF(ISBLANK(D25),"",VLOOKUP(D25,LawDb1[[Matter'#]:[Matter Name]],2,FALSE))
I would just use...
=IF(D25="","",VLOOKUP(D25,LawDb1[[Matter'#]:[Matter Name]],2,FALSE))

Thank you for replying. Seeing what you added made me realize that I was overthinking things yesterday.

=IF(ISBLANK(B2),"",IF(ISNUMBER(MATCH(B2,LawDb1!$B$19:$B$38,0)),LawDb1!$I$3,IF(ISNUMBER(MATCH(B2,LawDb1!$B$2:$B$18,0)),LawDb1!$I$2)))

now the formula works the way that I want it too. I cannot thank all of you enough for the help that you gave me.

 

[FDibbins]

Your 3rd IF statement is missing the last argument, and Im not really sure it is even needed? If there will always be a match for either B2:B18 OR B19:B38, then maybe just this...

=IF(B2="","",IF(ISNUMBER(MATCH(B2,LawDb1!$B$19:$B$38,0)),LawDb1!$I$3,LawDb1!$I$2))
or even this...
=IF(B2="","",IF(iserror(MATCH(B2,LawDb1!$B$19:$B$38,0)),LawDb1!$I$2,LawDb1!$I$3))