MATCH INDEX formula required.

[Gordon]

Hi

Any help with this sould be great. I use to be able to nail this stuff, but
too long working with VBA has screwed my mind.

I have 3 columns of data (D4:F6000) that forms my table. There are loads of
repetetive data though no row of 3 cells holds the same info.

In cell G4 I enter the values say from D5
In cell H4 I enter the values say from E5
In cell I4 I need a formaula that will auto match the data from F5?

Been going round the bend on this. Can anyone clear the mist for me?

Many thanks

Gordon

 

[Eduardo]

Hi,
=SUMPRODUCT(--(D=G3),--(E:E=H3),(F:F))
if was helpful please say yes. thank you

 

[T. Valko]

Try this array formula** . This works if the value to be returned is either
text or numeric.

=INDEX(F4:F6000,MATCH(1,(D46000=G4)*(E4:E6000=H4),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If the value to be returned in always numeric, try this normally entered
formula:

=SUMPRODUCT(--(D46000=G4),--(E4:E6000=H4),F4:F6000)

If you're using Excel 2007 and the value to be returned in always numeric:

=SUMIFS(F4:F6000,D46000,G4,E4:E6000,H4)

 

[Gordon]

Hi there...

this returned the value #NUM!

I thought match index would have been better?

Cheers

G