If Statement

MrNerd

New Member
Joined
Jan 9, 2018
Messages
29
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?)

ABCDEFG
1Test Group123456
2ARCFUnclearRCFRCFRCFRCF
3BCACACACAUnclearCA
4CPTUnclearPTPTPTUnclear
5DTRDTRDUnclearTEDTRDTRD
6ELCFLCFUnclearLCFLCFLCF
7FFSTFSTFSTFSTUnclearFST
8GSHSPTTRDUnclearFSTSHS
9HRFRFRFUnclearRFRF
10ILFSHSUnclearLFSHSUnclear
11
12
13
14Test1
15
16
17ARCF
18BCA
19CPT
20DTRD
21ELCF
22FFST
23GSHS
24HRF
25ILF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Re: If Statement Help

Hi,

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Test Group</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A</td><td style=";">RCF</td><td style=";">Unclear</td><td style=";">RCF</td><td style=";">RCF</td><td style=";">RCF</td><td style=";">RCF</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">B</td><td style=";">CA</td><td style=";">CA</td><td style=";">CA</td><td style=";">CA</td><td style=";">Unclear</td><td style=";">CA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">C</td><td style=";">PT</td><td style=";">Unclear</td><td style=";">PT</td><td style=";">PT</td><td style=";">PT</td><td style=";">Unclear</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">D</td><td style=";">TRD</td><td style=";">TRD</td><td style=";">Unclear</td><td style=";">TED</td><td style=";">TRD</td><td style=";">TRD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">E</td><td style=";">LCF</td><td style=";">LCF</td><td style=";">Unclear</td><td style=";">LCF</td><td style=";">LCF</td><td style=";">LCF</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">F</td><td style=";">FST</td><td style=";">FST</td><td style=";">FST</td><td style=";">FST</td><td style=";">Unclear</td><td style=";">FST</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">G</td><td style=";">SHS</td><td style=";">PT</td><td style=";">TRD</td><td style=";">Unclear</td><td style=";">FST</td><td style=";">SHS</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">H</td><td style=";">RF</td><td style=";">RF</td><td style=";">RF</td><td style=";">Unclear</td><td style=";">RF</td><td style=";">RF</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">I</td><td style=";">LF</td><td style=";">SHS</td><td style=";">Unclear</td><td style=";">LF</td><td style=";">SHS</td><td style=";">Unclear</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Test</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">A</td><td style=";">Unclear</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">B</td><td style=";">CA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">C</td><td style=";">Unclear</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">D</td><td style=";">TRD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">E</td><td style=";">LCF</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">F</td><td style=";">FST</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">G</td><td style=";">PT</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">H</td><td style=";">RF</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">I</td><td style=";">SHS</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet97</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B17</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A17,A$2:G$10,B$14+1,0</font>)</td></tr></tbody></table></td></tr></table><br />

B17 formula copied down.
 

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767
Re: If Statement Help

A
B
C
D
E
F
G
1
Test Group
1​
2​
3​
4​
5​
6​
2
ARCFUnclearRCFRCFRCFRCF
3
BCACACACAUnclearCA
4
CPTUnclearPTPTPTUnclear
5
DTRDTRDUnclearTEDTRDTRD
6
ELCFLCFUnclearLCFLCFLCF
7
FFSTFSTFSTFSTUnclearFST
8
GSHSPTTRDUnclearFSTSHS
9
HRFRFRFUnclearRFRF
10
ILFSHSUnclearLFSHSUnclear
11
12
13
14
Test
1​
15
16
17
ARCF
18
BCA
19
CPT
20
DTRD
21
ELCF
22
FFST
23
GSHS
24
HRF
25
ILF

<tbody>
</tbody>


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

 

Watch MrExcel Video

Forum statistics

Threads
1,109,450
Messages
5,528,819
Members
409,839
Latest member
akashsadhu
Top