May 08 2019 01:11 PM
Hello,
I want to auto populate a cell based on the info below.
If some one types in :
A01 I want the cell to say ARPT
Example 2:
If someone types
I04 I want to the cell to say INFL
is there a way to do this?
I want the top 9 categories to auto populate in a cell based on the codes given below its category
.
ARPT | INFL | IT | SysOps | LAE | MX | SEC | FO | Crew Svc |
A01 | I01 | IT1 | S01 | S16 | T01 | SEC1 | F01 | CR12 |
A01B | I02 | IT2 | S02 | T01L | SEC2 | F02 | CR2F | |
A03 | I04 | IT3 | S03 | T01P | SEC3 | F04 | CR2I | |
A04 | I05 | IT4 | S04 | T02 | SEC4 | F06 | CR4 | |
A05 | I06 | IT5 | S05 | T02B | SEC5 | F07 | CR5 | |
A05B | I17 | IT6 | S06D | T02H | SEC6 | F09 | CR5F | |
A06 | I17C | S06O | T02P | SEC7 | F09B | CR5I | ||
A06B | I29 | S06T | T03 | F09T | CR6 | |||
A06I | S06W | T05 | F29 | CR6F | ||||
A07 | S08 | T06 | CR6I | |||||
A07B | S09D | T11 | CR8 | |||||
A08 | S09O | T12 | CR8F | |||||
A09 | S10 | T29 | CR8I | |||||
A10 | S10R | T29B | CR8T | |||||
A11 | S10S | CR9 | ||||||
A11D | S11 | CR9F | ||||||
A12 | S12C | CR9I | ||||||
A15 | S12E | |||||||
A15B | S12M | |||||||
A16 | S12T | |||||||
A16B | S12W | |||||||
A17 | S22 | |||||||
A17B | S24 | |||||||
A18 | S24J | |||||||
A18B | S29 | |||||||
A19H | S30 | |||||||
A19J | S31 | |||||||
A21 | S32 | |||||||
A21B | S35 | |||||||
A23 | ||||||||
A24 | ||||||||
A26 | ||||||||
A26B | ||||||||
A28 | ||||||||
A28B | ||||||||
A29 | ||||||||
A29B | ||||||||
A31 | ||||||||
A31B | ||||||||
A32 |
May 08 2019 01:39 PM
Solution
Hi John,
For such location
it'll be
=IFERROR(INDEX($B$2:$J$2,SUMPRODUCT(($B$3:$J$42=$M$2)*COLUMN($B$3:$J$42))-COLUMN($B$2)+1),"no such")
Please see attached.
May 08 2019 01:52 PM
Thank You , This worked out.
May 08 2019 02:51 PM
@John Sokolowski , you are welcome
May 08 2019 01:39 PM
Solution
Hi John,
For such location
it'll be
=IFERROR(INDEX($B$2:$J$2,SUMPRODUCT(($B$3:$J$42=$M$2)*COLUMN($B$3:$J$42))-COLUMN($B$2)+1),"no such")
Please see attached.