MATCH INDEX formula required.

  • Thread starter Thread starter Gordon
  • Start date Start date
G

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
 
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)
 
Hi there...

this returned the value #NUM!

I thought match index would have been better?

Cheers

G
 
Back
Top