Lookup variation

  • Thread starter Thread starter R. Todd Miller
  • Start date Start date
R

R. Todd Miller

I'm Stuck. I am trying to do a lookup as follows...
I know the Area and Pers. How can I tell which Col/Group the person is in?

Cell A1 = "Area2"
Cell A2 = "PersA"
Need Cell A3 to equal "Group3" or "ColD" or even the column number 4.

ColA ColB ColC ColD ColE
Group1 Group2 Group3 Group4
Area1 PersB PersG PersD PersA
Area1 PersE PersC PersF PersH
Area1 PersJ PersK PersI PersL

Area2 PersG PersC PersK PersD
Area2 PersF PersI PersA PersH
Area2 PersL PersB PersE PersJ

Area3 PersI PersB PersK PersA
Area3 PersE PersD PersH PersJ
Area3 PersG PersF PersL PersC

Thanks in advance for any help.
 
Hi Todd,

There may be a better way, but here's what I have...

=INDEX(A1:E1,SUMPRODUCT((OFFSET(B2:E4,MATCH(A15,A2:A12,0)-1,0)=A16)*COLUM
N(OFFSET(B2:E4,MATCH(A15,A2:A12,0)-1,0))))

where A15 contains the area of interest and A16 contains the person of
interest.

Hope this helps!
 
Thanks Domenic. That works perfectly. Now I have to study your
formula and figure out exactly what's going on here. Thanks again.
 
Back
Top