MATCH INDEX formula required.
 

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

MATCH INDEX formula required.
 

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

"Gordon" wrote:

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

MATCH INDEX formula required.
 

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

=INDEX(F4:F6000,MATCH(1,(D4:D6000=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(--(D4:D6000=G4),--(E4:E6000=H4),F4:F6000)

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

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


--
Biff
Microsoft Excel MVP


"Gordon" wrote in message
...
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

MATCH INDEX formula required.
 

Hi there...

this returned the value #NUM!

I thought match index would have been better?

Cheers

G

"Eduardo" wrote:

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

"Gordon" wrote:

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