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 typesI04 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 . ARPTINFLITSysOpsLAEMXSECFOCrew SvcA01I01IT1S01S16T01SEC1F01CR12A01BI02IT2S02 T01LSEC2F02CR2FA03I04IT3S03 T01PSEC3F04CR2IA04I05IT4S04 T02SEC4F06CR4A05I06IT5S05 T02BSEC5F07CR5A05BI17IT6S06D T02HSEC6F09CR5FA06I17C S06O T02PSEC7F09BCR5IA06BI29 S06T T03 F09TCR6A06I S06W T05 F29CR6FA07 S08 T06 CR6IA07B S09D T11 CR8A08 S09O T12 CR8FA09 S10 T29 CR8IA10 S10R T29B CR8TA11 S10S CR9A11D S11 CR9FA12 S12C CR9IA15 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

John Sokolowski 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.

John Sokolowski

Copper Contributor

May 08, 2019

Solved

Hlookup multiple criteria and rows help please

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

excel

Formulas and Functions

SergeiBaklan
May 08, 2019
John Sokolowski

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.
2D column name.xlsx13 KB

Forum Discussion

Hlookup multiple criteria and rows help please

3 Replies

Resources