SOLVED

# Hlookup multiple criteria and rows help please

Highlighted
Occasional Contributor

# 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
3 Replies
Solution

# Re: Hlookup multiple criteria and rows help please

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")`