Vlookup question

[tycasey17]

_ENLR and _NO are listed in the name manager with the information below it. The basic thing I'm trying to figure out is how to do the formula. I had it in an excel before I just don't know how I lost it. I think it used MATCH and ISNA.

If a Soldier is RANK and STATUS matchs the one of the red words in each column (_ENLR and _NO), then I want it to say ORDER. If it doesn't match one of the red words in each column then I want it to say NO ORDER.

[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]RANK[/TD]
[TD]STATUS[/TD]
[TD]OUTCOME[/TD]
[TD][/TD]
[TD]_ENLR[/TD]
[TD]_NO[/TD]
[/TR]
[TR]
[TD]DOE, JOHN[/TD]
[TD]SGT[/TD]
[TD]TPU[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD]PVT[/TD]
[TD]TPU[/TD]
[/TR]
[TR]
[TD]DOE, JANE[/TD]
[TD]CPT[/TD]
[TD]TPU[/TD]
[TD]NO ORDER[/TD]
[TD][/TD]
[TD]PFC[/TD]
[TD]AGR[/TD]
[/TR]
[TR]
[TD]SNUFFY, JOE[/TD]
[TD]PVT[/TD]
[TD]AGR[/TD]
[TD]NO ORDER[/TD]
[TD][/TD]
[TD]SPC[/TD]
[TD]IET[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SGT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SSG[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Leandroarb]

_ENLR and _NO are listed in the name manager with the information below it. The basic thing I'm trying to figure out is how to do the formula. I had it in an excel before I just don't know how I lost it. I think it used MATCH and ISNA.

If a Soldier is RANK and STATUS matchs the one of the red words in each column (_ENLR and _NO), then I want it to say ORDER. If it doesn't match one of the red words in each column then I want it to say NO ORDER.

[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]RANK[/TD]
[TD]STATUS[/TD]
[TD]OUTCOME[/TD]
[TD][/TD]
[TD]_ENLR[/TD]
[TD]_NO[/TD]
[/TR]
[TR]
[TD]DOE, JOHN[/TD]
[TD]SGT[/TD]
[TD]TPU[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD]PVT[/TD]
[TD]TPU[/TD]
[/TR]
[TR]
[TD]DOE, JANE[/TD]
[TD]CPT[/TD]
[TD]TPU[/TD]
[TD]NO ORDER[/TD]
[TD][/TD]
[TD]PFC[/TD]
[TD]AGR[/TD]
[/TR]
[TR]
[TD]SNUFFY, JOE[/TD]
[TD]PVT[/TD]
[TD]AGR[/TD]
[TD]NO ORDER[/TD]
[TD][/TD]
[TD]SPC[/TD]
[TD]IET[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SGT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SSG[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi,
Try this:

{=IF(SUM(IF(($B3:$C3=F3)+($B3:$C3=G3),1,0))>0,"ORDER","NO ORDER")}

Press CTRL + SHIFT + ENTER to work.

The columns are:

A: Name
B: Rank
C: Status
D: OutCome
E: _ENLR
F: _NO

 

[tycasey17]

I appreciate your assistance.

I actually found what I was looking for and this is what I was actually using. It was ISNUMBER not ISNA

IF(OR(ISNUMBER(MATCH($B4,_ENLR,0)),ISNUMBER(MATCH($E4,_NO,0))),DB!$AA$31,

 

[Leandroarb]

I appreciate your assistance.

I actually found what I was looking for and this is what I was actually using. It was ISNUMBER not ISNA

IF(OR(ISNUMBER(MATCH($B4,_ENLR,0)),ISNUMBER(MATCH($E4,_NO,0))),DB!$AA$31,

That's good!
Thanks for the feedback!
Cheers!