SOLVED

Hlookup multiple criteria and rows help please

Copper Contributor

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

 

.

 

ARPTINFLITSysOpsLAEMXSECFOCrew Svc
A01I01IT1S01S16T01SEC1F01CR12
A01BI02IT2S02 T01LSEC2F02CR2F
A03I04IT3S03 T01PSEC3F04CR2I
A04I05IT4S04 T02SEC4F06CR4
A05I06IT5S05 T02BSEC5F07CR5
A05BI17IT6S06D T02HSEC6F09CR5F
A06I17C S06O T02PSEC7F09BCR5I
A06BI29 S06T T03 F09TCR6
A06I  S06W T05 F29CR6F
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        
3 Replies
best response confirmed by John Sokolowski (Copper Contributor)
Solution

@John Sokolowski 

 

Hi John,

 

For such location

image.png

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.

Thank You , This worked out.

1 best response

Accepted Solutions
best response confirmed by John Sokolowski (Copper Contributor)
Solution

@John Sokolowski 

 

Hi John,

 

For such location

image.png

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.

View solution in original post