lookups in excel with weird formatting

**ammartino44** · 05-19-2015, 08:09 PM

Hello I'm trying to a lookup table but I'm not sure how to do it based on the formatting (attached is a sample). Thank you.

**daffodil11** · 05-19-2015, 08:22 PM

=sumproduct((a4:a9=j4)*(a5:a10=k3)*(b5:b10))

or

=lookup(2,1/((a4:a13=j4)*(a5:a14=k3)),b5:b14)

**ammartino44** · 05-20-2015, 01:21 PM

That doesn't work. It returns errors.

**daffodil11** · 05-20-2015, 02:36 PM

How about INDIRECT instead?

=INDIRECT("B" & MATCH(K$3,OFFSET($A$1:$A$3,MATCH($J4,$A$1:$A$13,0),0),0)+MATCH($J4,$A$1:$A$13,0))

**ammartino44** · 05-21-2015, 01:09 PM

boom boom pau. Thanks.

**daffodil11** · 05-21-2015, 01:17 PM

No problemo

Re: lookups in excel with weird formatting