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

 

[Oeldere]

With Vlookup.

Book1
	A	B	C	D	E	F	G	H	I
1	Date	REQUESTOR	Practice Area	Matter #	Matter Name	Type of Filing	New or Existing	DOC	Location
2	4-feb	LB	COM	LA2016000123	You v. Me	Correspondence	Existing	4-feb	CP
3	4-mrt	DZ	REG	LA2016000456	TL567	Pleadings	New	4-mrt	HQ
Blad1

Book1
	A	B
1
2	LB	CP
Blad9

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,Blad1!$B$2:$I$3,8,0)

 

[SpillerBD]

Your if statement does not make an evaluation as there is no = or <> signs, so match does not return True or False but a number. Try wrapping an ISERROR around the Match Statement though so you do evaluate a TRUE/FALSE

 

[Jonmo1]

MATCH doesn't return a True or False value.
It returns a NUMBER, an index # to be exact, relative to the position the lookup value is found in the range.
It returns #N/A if the match isn't found.

You have to test either for Not an Error, or Is a Number.

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

 

[Nickel17]

Thank you for your reply. I'm not familiar with using ISERROR so when I tried

IF(ISERROR[COLOR=#333333](MATCH(B2,LawDb1!$B$16:$B$35,0),LawDb1!$I$3,"")[/COLOR]

I get an error telling me that I have "too few arguments". Can you point out where I went wrong, please?

 

[Nickel17]

With Vlookup.

	A	B	C	D	E	F	G	H	I
Date	REQUESTOR	Practice Area	Matter #	Matter Name	Type of Filing	New or Existing	DOC	Location
LB	COM	LA2016000123	You v. Me	Correspondence	Existing	CP
DZ	REG	LA2016000456	TL567	Pleadings	New	HQ

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]4-feb[/TD]

[TD="align: right"]4-feb[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4-mrt[/TD]

[TD="align: right"]4-mrt[/TD]

</tbody>

Blad1​

	A	B
LB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #92D050"]CP[/TD]

</tbody>

Blad9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=VLOOKUP(A2,Blad1!$B$2:$I$3,8,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your reply. It did not work, so I must have messed something up.

 

[Jonmo1]

Can you point out where I went wrong, please?

You didn't use the formula I posted.
You're missing a ), there should be 2 )) after the 0

Copy the formula directly from my post into your cell.

 

[Nickel17]

MATCH doesn't return a True or False value.
It returns a NUMBER, an index # to be exact, relative to the position the lookup value is found in the range.
It returns #N/A if the match isn't found.

You have to test either for Not an Error, or Is a Number.

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

Hi. Thank you for your reply. This worked! But only for "HQ". Is there a way to

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

with

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

with an ISBLANK statement?

=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))

This is what I came up with and it works! Thank you!

 

[Jonmo1]

I don't understand what you're asking now about isblank

 

[Nickel17]

I don't understand what you're asking now about isblank

I'm sorry, should have been more clear. I usually have my formulas in a few hundred cells in a column as I never know how long the active worksheet is from month to month. I like to have a blank cell if there is no data in any of the other cells because my Boss doesn't like looking at N/A or other miscellaneous values in this sheet.

For example, I have this in the Matter Name Column.

=IF(ISBLANK(D25),"",VLOOKUP(D25,LawDb1[[Matter'#]:[Matter Name]],2,FALSE))