Index Formula returning unwanted results due to similar cell values

[u4carson]

The following Index formula returns both "Assessing Official" and "Assessing Official Rep" ... I am hoping to limit the results to only "Assessing official"... any assistance would be greatly appreciated.

=INDEX($V:$V, MATCH($O2&"Assessing Official",$O:$O&$W:$W, 0))

 

[SpillerBD]

You are using CTRL+SHIFT+ENTER to record the formula?

 

[u4carson]

Thank you for the response... yes, I am using CTRL+SHIFT+ENTER... the issue is that it is returning a result for both "assessing official" and "assessing official rep" ... I assume because both contain the text "assessing official." I need for it to only return a result for cells that exactly match "Assessing Official." FYI, the only two instances containing that text are these two, so omitting cells that contain "Rep" would work too.

 

[Aladin Akyurek]

What is the value in O2?

 

[Oscar Cronquist]

Try using a delimiting character:

=INDEX($V:$V, MATCH($O2&"|Assessing Official",$O:$O&"|"&$W:$W, 0))

 

[u4carson]

No luck with the above... I should have posted more information... there is likely a better solution than mine.


I am looking to have column (K) populate with user name (V) when there is an "Assessing Official" (W) associated with the contract number (O) for every instance of the contract number. In the example below BBBBBB2 shows Alan on every occurrence of BBBBBB2. Also, CCCCCC3 has no one listed because Lauren is an "Assessing Official REP" not an "Assessing Official."

K	O	V	W
Assessing Official	All Contract Numbers	User Name	User Role
AMBER	AAAAAA1	AMBER	Assessing Official
ALAN	BBBBBB2	LAUREN	Assessing Official Rep
ALAN	BBBBBB2	ALAN	Assessing Official
ALAN	BBBBBB2	AMY	Reviewing Official
	CCCCCC3	LAUREN	Assessing Official Rep

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

 

[Oscar Cronquist]

	A​	B​	C​	D​
1​	Assessing Official	All Contract Numbers	User Name	User Role
2​	AMBER	AAAAAA1	AMBER	Assessing Official
3​	ALAN	BBBBBB2	LAUREN	Assessing Official Rep
4​	ALAN	BBBBBB2	ALAN	Assessing Official
5​	ALAN	BBBBBB2	AMY	Reviewing Official
6​		CCCCCC3	LAUREN	Assessing Official Rep

Sheet: Sheet64

Array formula in cell A2:

=IFNA(INDEX($C$2:$C$6,MATCH(B2&"Assessing Official",$B$2:$B$6&$D$2:$D$6,0)),"")

 

[u4carson]

=IFNA(INDEX($C$2:$C$6,MATCH(B2&"Assessing Official",$B$2:$B$6&$D$2:$D$6,0)),"")

This worked, thank you!