Index Match Question: Merged Cells & Variables

[iSuwi]

Hi there,

I have two excel sheets; Test Sheet and Variable Sheet. I'm using an index match function so that it matches two values from Variable Sheet and matches it to Test Sheet, resulting in data from a certain cell in a column range. Now, the only variable data that will change is "A7", which is grabbed from Variable Sheet.


Formula being used:


=INDEX('[Test Sheet.xlsx]Sheet'!$D$88:$D$25717,MATCH(1,INDEX(('[Test Sheet.xlsx]Sheet'!
$A$88:$A$25717=A3)*('[Test Sheet.xlsx]Sheet'!$B$88:$B$25717=$A7),0,1),0))

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
That variable will change several times as there are different lookup codes in my Variable Worksheet. For example, A7-A19 is ONE merged cell, so it wouldn't matter if I used A7 or A18 as a value. Then, it goes on to another cell range; for example, A20-A23, all being the same value in one merged cell.

<o></o>
Is there any way I can make it so that the second variable, A7, will automatically skip to the next merged cell(s), instead of manually changing that value per paste?

Note: When I do drag it down, it only skips to the next cell; not the next merged cell.

<o></o>
Thank you.