Using an Array formula within a standard one

[pjwhitfield]

Firstly, Im a self confessed Array Formula dimwit!

Im looking to find the last occurence of a particular item in sheet and display the cell 3 along from it. A quick google gave me an array formula which works fine to return the relevant row number

Please Login or Register  to view this content.

So in C8 I have the word "Car" and it correctly returns 8 as the answer because row 8 in the Case Notes is the last row referring to "Car"

What I want however is to return the Note in column D of row 8 so I figured I could just add in the Offset to return the wanted cell

Please Login or Register  to view this content.

however when I do that (and enter it as an Array function) it returns the data from Cell(1,8) ie it doesnt run the array function bit as an array (and I can understand that because the addition of the OFFSET totally changes the array function).

I can get around this with a hidden column where I'll just have the Array formula and then refer to that in the OFFSET but is that the best way to do this or is there a simple way mix Array and normal formulas?

[Special-K]

OFFSET's first paramater should be a cell reference.

Try
OFFSET(C1,LARGE((('Case Notes'!C:C)=C8)*ROW('Case Notes'!C:C),1),-4)

You might have to adjust the column/row by 1 to get the value exactly.

[bernieburnham]

Hi,

The alternative is to use the INDEX and MATCH functions, which seem to work when I tried a mini-example.

Please Login or Register  to view this content.

For performance issues, it is always worth capping your table size rather than using an entire column (i.e.: $C$1:$C$1000 rather than $C:$C).

Hope this helps,

---
Bernieburnham

[bebo021999]

So you'd like to search the last "Car" (word in cell C8) in C column then get data in D column?

=LOOKUP(2,1/('Case Notes'!C:C=C8),'Case Notes'!D:D)