index match lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rplp81 - 15 Nov 2006 19:47 GMT

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?

Reply to this Message

Bernard Liengme - 15 Nov 2006 20:23 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

>I have a table that resembles below:
>
[quoted text clipped - 12 lines]
> =index(c2:c7,match(e2&f2,a2:a7&b2:b7,0))
> Any advice?

Reply to this Message

Rate this thread: