Match and Index Help

[20did99]

I need help with Match and Index.

The problem is - I have a table I need the Index formula to copy down to each row. The problem I having is that the formula is copying down however, the array reference is moving down by one else well.

For example

Row 1 in my table will show =INDEX(Sheet1!A1:D6,MATCH(1,(Sheet1!A:A=G3)*(Sheet1!B:B=H3)*(Sheet1!C:C=I3),0),4)

Row 2 in my table will show =INDEX(Sheet1!A2:D6,MATCH(1,(Sheet1!A:A=G3)*(Sheet1!B:B=H3)*(Sheet1!C:C=I3),0),4).

Can someone help.

[Pepe Le Mokko]

Hi and welcome
is there a reason why you posted in a Word forum?

[Pepe Le Mokko]

To solve your problem have a look at absolute and relative cell/range references

[AliGW]

Moved to the appropriate sub-forum.

[AliGW]

You need this:

=INDEX(Sheet1!$A$1:$D$6,MATCH(1,(Sheet1!A:A=G3)*(Sheet1!B:B=H3)*(Sheet1!C:C=I3),0),4)

[Glenn Kennedy]

and... don't use whole column references in MATCH, when it's part of an array formula. It will look at 1,000,000 rows every time it sees one of them. Use a reasonable , future-proof range.