Excel 2007 : Extracting values from specific row and column with criteria

[meslija]

Hello,
I wrote in the attachment what i need.I cant explain very well what i want.
I hope you will understand.

[zbor]

Here, try this:

[meslija]

thank you.

[Marcol]

A bit late, and zbor beat me to it, however it's done.

This uses array formulae (non-volatile) and dynamic named ranges.

You should be able to add further data to your list and validation list without having to change any formulae.
Remember the formulae in I6, K6, and M6 are arrays and should be confirmed with Ctrl+Shift+Enter

Hope this helps

[zbor]

However Marcol, the INDEX solution you've applied should be used instead of OFFSET.

Enter this formula in I6 (with normal enter):

=INDEX($C$2:$C$100,MATCH($K$3&$K$4,INDEX($A$2:$A$100&$B$2:$B$100,,),0)+ROWS($I$6:I6)-1,0)

Change red array to $D$2:$D$100 and $E$2:$E$100 respectively

[Marcol]

@ zbor

Sorry, I don't follow your thought.
I was using dynamic names rather than fixed ranges

Your suggestion, using names becomes

Please Login or Register  to view this content.

This is still an array function

What am I missing?

[zbor]

I've changed my OFFSET approach with INDEX approach.