Regarding Index & Match function problem

Hi,

I am using Index match function to match the variable. For example, in the attached excel sheet (match-index.xls)

Sheet1 - consist of following columns

* Date
* Ref no
* Impressions
* Profiles (extracted from sheet2)


Sheet2 - consist of

* Date
* Ref no
* Profiles

I used the following index & Match function to copy profile from sheet2. the function has follows

=INDEX(Sheet2!C2:C7,MATCH(Sheet1!A2,Sheet2!A2:A7,0),MATCH(B2,Sheet2!B2:B7,0))

I am comparing Date & Ref. no from the two sheets and copy the profiles details from sheet 2 to sheet1

The problem

For "16-Aug-08" Date & "102" Ref. No, the above match-index function is reuturning wrong value. Instead of returning 84, it is returning 58 (that i highlighted in red). Please Help, how to overcome?

Hi...your problem is you are not "fixing" ($), your celll references in your formulae, try this:-

Please Login or Register  to view this content.

spreadsheet enclosed

Hi Stud,

Thanks for your reply. But this formula is not working when the previous date is called again, please find attached the excel sheet (index-match2.xls). please help. Thanks in advance

Hi Ramki

If you change your formula to be an array formula, it will give you the results that you require.

Don't forget to hold Ctrl + Shift, and press the Enter key to see the result.

I have attached your spreadsheet with the amended formulas.

spellbound