If Statement

[MrNerd]

What I would like to do is give me the results for each person (A,B,C...) based on which test we are looking at. In cell B14, I would like to put in the number of which test and have it use a formula to tell me which result. In the current example, I show test 1 being used in B14 and below are what I would like to auto populate using a formula. Since each person can only qualify for one option and after that option is used, no other person can be that for that specific test. (make sense?)

	A	B	C	D	E	F	G
Test Group
A
B
C
D
E
F
G
H
I

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

[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]

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

[TD="align: center"]RCF[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Unclear[/TD]
[TD="align: center"]RCF[/TD]
[TD="align: center"]RCF[/TD]
[TD="align: center"]RCF[/TD]
[TD="align: center"]RCF[/TD]

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

[TD="align: center"]CA[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]CA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Unclear[/TD]
[TD="align: center"]CA[/TD]

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

[TD="align: center"]PT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Unclear[/TD]
[TD="align: center"]PT[/TD]
[TD="align: center"]PT[/TD]
[TD="align: center"]PT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Unclear[/TD]

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

[TD="align: center"]TRD[/TD]
[TD="align: center"]TRD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Unclear[/TD]
[TD="align: center"]TED[/TD]
[TD="align: center"]TRD[/TD]
[TD="align: center"]TRD[/TD]

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

[TD="align: center"]LCF[/TD]
[TD="align: center"]LCF[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Unclear[/TD]
[TD="align: center"]LCF[/TD]
[TD="align: center"]LCF[/TD]
[TD="align: center"]LCF[/TD]

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

[TD="align: center"]FST[/TD]
[TD="align: center"]FST[/TD]
[TD="align: center"]FST[/TD]
[TD="align: center"]FST[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Unclear[/TD]
[TD="align: center"]FST[/TD]

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

[TD="align: center"]SHS[/TD]
[TD="align: center"]PT[/TD]
[TD="align: center"]TRD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Unclear[/TD]
[TD="align: center"]FST[/TD]
[TD="align: center"]SHS[/TD]

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

[TD="align: center"]RF[/TD]
[TD="align: center"]RF[/TD]
[TD="align: center"]RF[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Unclear[/TD]
[TD="align: center"]RF[/TD]
[TD="align: center"]RF[/TD]

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

[TD="align: center"]LF[/TD]
[TD="align: center"]SHS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Unclear[/TD]
[TD="align: center"]LF[/TD]
[TD="align: center"]SHS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Unclear[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]Test[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]RCF[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]CA[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]PT[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]TRD[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]LCF[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]FST[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]SHS[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]RF[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]LF[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

 

[jtakw]

Re: If Statement Help

Hi,

I'm Not sure if I understand your requirements, it's a bit "unclear":

Book1
	A	B	C	D	E	F	G
1	Test Group	1	2	3	4	5	6
2	A	RCF	Unclear	RCF	RCF	RCF	RCF
3	B	CA	CA	CA	CA	Unclear	CA
4	C	PT	Unclear	PT	PT	PT	Unclear
5	D	TRD	TRD	Unclear	TED	TRD	TRD
6	E	LCF	LCF	Unclear	LCF	LCF	LCF
7	F	FST	FST	FST	FST	Unclear	FST
8	G	SHS	PT	TRD	Unclear	FST	SHS
9	H	RF	RF	RF	Unclear	RF	RF
10	I	LF	SHS	Unclear	LF	SHS	Unclear
11
12
13
14	Test	2
15
16
17	A	Unclear
18	B	CA
19	C	Unclear
20	D	TRD
21	E	LCF
22	F	FST
23	G	PT
24	H	RF
25	I	SHS
Sheet97

Cell Formulas
Range	Formula
B17	=VLOOKUP(A17,A$2:G$10,B$14+1,0)

B17 formula copied down.

 

[MARZIOTULLIO]

Re: If Statement Help

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Test Group[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]A[/TD]
[TD]RCF[/TD]
[TD]Unclear[/TD]
[TD]RCF[/TD]
[TD]RCF[/TD]
[TD]RCF[/TD]
[TD]RCF[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]B[/TD]
[TD]CA[/TD]
[TD]CA[/TD]
[TD]CA[/TD]
[TD]CA[/TD]
[TD]Unclear[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]C[/TD]
[TD]PT[/TD]
[TD]Unclear[/TD]
[TD]PT[/TD]
[TD]PT[/TD]
[TD]PT[/TD]
[TD]Unclear[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]D[/TD]
[TD]TRD[/TD]
[TD]TRD[/TD]
[TD]Unclear[/TD]
[TD]TED[/TD]
[TD]TRD[/TD]
[TD]TRD[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]E[/TD]
[TD]LCF[/TD]
[TD]LCF[/TD]
[TD]Unclear[/TD]
[TD]LCF[/TD]
[TD]LCF[/TD]
[TD]LCF[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]F[/TD]
[TD]FST[/TD]
[TD]FST[/TD]
[TD]FST[/TD]
[TD]FST[/TD]
[TD]Unclear[/TD]
[TD]FST[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]G[/TD]
[TD]SHS[/TD]
[TD]PT[/TD]
[TD]TRD[/TD]
[TD]Unclear[/TD]
[TD]FST[/TD]
[TD]SHS[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]H[/TD]
[TD]RF[/TD]
[TD]RF[/TD]
[TD]RF[/TD]
[TD]Unclear[/TD]
[TD]RF[/TD]
[TD]RF[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]I[/TD]
[TD]LF[/TD]
[TD]SHS[/TD]
[TD]Unclear[/TD]
[TD]LF[/TD]
[TD]SHS[/TD]
[TD]Unclear[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]Test[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]A[/TD]
[TD]RCF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]B[/TD]
[TD]CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]C[/TD]
[TD]PT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]D[/TD]
[TD]TRD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]E[/TD]
[TD]LCF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]F[/TD]
[TD]FST[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]G[/TD]
[TD]SHS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]H[/TD]
[TD]RF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]I[/TD]
[TD]LF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


B17=VLOOKUP($A17,$A$1:$G$10,MATCH($B$14,$A$1:$G$1,0),FALSE) copy down

 

[MrNerd]

Re: If Statement Help

Thanks! That worked well.