Index Match 2 columns 1 row

D

deeds

I am sure this is out there somewhere...I just can't find it.

A B C D E
Cage Box Kennel
Dog 2008 5 6 7
Cat 2008 4 3 2
Fish 2008 8 9 1

I need to find the number 3:
Cat, 2008, Box=3

How do I get it with Index & Match?
Thanks in advance
 
T

T. Valko

Try this:

A10 = cat
B10 = 2008
C10 = box

=SUMPRODUCT(--(A2:A4=A10),--(B2:B4=B10),INDEX(C2:E4,,MATCH(C10,C1:E1,0)))
 
M

Max

How do I get it with Index & Match?

One way using the above ..

Your table is assumed in A1:E4

Inputs in
A10 = cat
B10 = 2008
C10 = box

In D10, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER:
=INDEX(C2:E4,MATCH(1,(A10=A2:A4)*(B10=B2:B4),0),MATCH(C10,C1:E1,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top