index match lookup
 

I have a table that resembles below:

G P R
1 8 10
1 4 11
2 3 12
2 7 13
3 8 14
3 4 15

G, P, and R are in columns a, b, and c starting in row 1.

I'm trying to use the equation below to take a person's entry of G (in e2)
and P (in f2) and return the R value, but it always returns an error.
=index(c2:c7,match(e2&f2,a2:a7&b2:b7,0))
Any advice?

index match lookup
 

You need to commit (finish) the formula with CTRL+SHIFT+ENTER not just ENTER
Then it is an array function which makes the a2:a7&b2:b7 part work (without
an array behaviour you get only one value)
Or you could use one of
=SUMPRODUCT(--(A2:A7=E2),--(B2:B7=F2),C2:C7) which returns 0 when there is
no match
=IF(SUMPRODUCT(--(A2:A7=E2),--(B2:B7=F2),C2:C7),SUMPRODUCT(--(A2:A7=E2),--(B2:B7=F2),C2:C7),NA())
which returns N/A if there is no match (or it 0 is matched in the R column
!!)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rplp81" wrote in message
...
I have a table that resembles below:

G P R
1 8 10
1 4 11
2 3 12
2 7 13
3 8 14
3 4 15

G, P, and R are in columns a, b, and c starting in row 1.

I'm trying to use the equation below to take a person's entry of G (in e2)
and P (in f2) and return the R value, but it always returns an error.
=index(c2:c7,match(e2&f2,a2:a7&b2:b7,0))
Any advice?