How To Use INDEX and MATCH with More Than One Lookup Criteria

[Aimee S.]

Hello Gurus.

=INDEX(PartsListData!$K$1:$T$500,MATCH("CAA Engineering",PartsListData!$K$1:$K$500,0),10) using CTRL SHIFT ENTER works with one criteria


How would I go about doing the same with the ability to check column K, and if "CAA Engineering" or "Technology Engineering" (key word being "or" since both will never exist at once, only one or the other will ever appear), return the integer value in column T which is 10 columns to the right?

I tried a method that separates the two conditions with an asterisk in the MATCH section but that produces a #VALUE error.

Help is always greatly appreciated.
Best,
Aimee

 

[RoryA]

Will there only ever be one match?

 

[Aimee S.]

100% Correct and I verified with the data customer just that point. Only one or the other, never both will appear, by nature of the CAA and Technology Engineering Processes being inherently distinct / unable to overlap.

 

[RoryA]

In that case I would use:

=LOOKUP(2,1/(("CAA Engineering"=PartsListData!$K$1:$K$500)+("Technology Engineering"=PartsListData!$K$1:$K$500)),PartsListData!$T$1:$T$500)

 

[Aimee S.]

That worked! Sorry for the delay - I was out of the office and my account is connected to my work email Thanks so much, Rory. This one sure caused a lot of head scratching!