Hlookup with multiple criteria

[Manny Singh]

Hi All,

Could you please help me write up a Hlookup formula with multiple criteria, I have described the criteria in attached sheet.

I will appreciate help.

Regards,
Manny

 

[Hui]

Manny

Does this work for you?
=CHOOSE(COUNTA(B2:C2)+1,"N/A",B2&C2,B2&" "&C2)

 

[r2c2]

@Manny Singh

Try this.

=CHOOSE(COUNT(MATCH({"GMAW","FCAW"},$B$2:$C$2,0))+1,NA(), IFERROR(HLOOKUP("GMAW",$B$2:$C$3,2,FALSE),HLOOKUP("FCAW",$B$2:$C$3,2,FALSE)),HLOOKUP("GMAW",$B$2:$C$3,2,FALSE)&HLOOKUP("FCAW",$B$2:$C$3,2,FALSE))

I tried to come up with something short and sweet, but couldn't. Then @Hui gave the idea.