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."




[TABLE="width: 626"]
<tbody>[TR]
[TD]K[/TD]
[TD]O[/TD]
[TD]V[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]Assessing Official[/TD]
[TD]All Contract Numbers[/TD]
[TD]User Name[/TD]
[TD]User Role[/TD]
[/TR]
[TR]
[TD]AMBER [/TD]
[TD]AAAAAA1[/TD]
[TD]AMBER [/TD]
[TD]Assessing Official[/TD]
[/TR]
[TR]
[TD]ALAN[/TD]
[TD]BBBBBB2[/TD]
[TD]LAUREN [/TD]
[TD]Assessing Official Rep[/TD]
[/TR]
[TR]
[TD]ALAN [/TD]
[TD]BBBBBB2[/TD]
[TD]ALAN [/TD]
[TD]Assessing Official[/TD]
[/TR]
[TR]
[TD]ALAN [/TD]
[TD]BBBBBB2[/TD]
[TD]AMY[/TD]
[TD]Reviewing Official[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]CCCCCC3[/TD]
[TD]LAUREN [/TD]
[TD]Assessing Official Rep[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Assessing Official[/td][td]All Contract Numbers[/td][td]User Name[/td][td]User Role[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]AMBER[/td][td]AAAAAA1[/td][td]AMBER[/td][td]Assessing Official[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]ALAN[/td][td]BBBBBB2[/td][td]LAUREN[/td][td]Assessing Official Rep[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]ALAN[/td][td]BBBBBB2[/td][td]ALAN[/td][td]Assessing Official[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]ALAN[/td][td]BBBBBB2[/td][td]AMY[/td][td]Reviewing Official[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td]CCCCCC3[/td][td]LAUREN[/td][td]Assessing Official Rep[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet64[/td][/tr][/table]

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!