Referencing a variable in an array range

[ChainsawDR]

Hi,

I'm successfully using the following array formula to check a cells value against a list (contained in I3:K3843):

Please Login or Register  to view this content.

However everytime I add another value to my list, I need to manually update this formula (from 3843 to 3844 for example). I'm looking to automate this...

In cell L2 I already have a count which outputs 3843. Does anyone know how I can reference this in my formula, aka

Please Login or Register  to view this content.

Any help would be greatly appreciated!!!

Thanks guys

ChainsawDR

[zbor]

Can you just exytend range:

{=INDEX($K$3:$K$10000,SUMPRODUCT(LARGE(--(ISNUMBER(FIND($J$3:$J$10000,B3)))*$I$3:$I$10000,1)))}

[ChainsawDR]

Thanks for the reply zbor. No unfortunately it requires it to be the exact range otherwise it produces #VALUE!

I've created a string that I've now started copying and pasting each time, but was hoping there might be a way of referencing that value in the count cell.

[zbor]

Can you upload example?