"RagDyeR" wrote in message
...
You probably revised the Column() function incorrectly!

That function is simply returning a number to the Index() function, which
deals with *relative* locations.

The Column() function has *nothing* to do with location.
Use it just to specify the *number of columns* in the array.
If you still have 6 columns in the array, *Don't* change it at all.
Leave it "Column(A:F)", no matter where you move to.


For example, if your new location is J12 to O30 (still 6 columns), use
this:

=IF(SUM(N(J13:O30=A22)),INDEX(J12:O12,MAX((J13:O30 =A22)*(COLUMN(A:F)))),"No
Match")


If it's this - J12 to R30 (9 columns), use this:

=IF(SUM(N(J13:R30=A22)),INDEX(J12:R12,MAX((J13:R30 =A22)*(COLUMN(A:I)))),"No
Match")

Don't forget - still an *array* formula - use CSE.
--

HTH,

RD

Very many thanks again - you are absolutely correct and that's exactly the
error I had made.

As an interim solution, I had simply added another worksheet and hence put
everything back in Cols A to B. But I will now use your solution.

Regards,

V