Thread: Criteria without using cells

A couple years ago I defined criteria that did not refer to cells. I don't recall how I did it but it's certainly not obvious. Anybody know how to do this? I tried a bunch of different syntax but can't get it. See attachment. I am trying to populate the Equity column. I've simplified it here. The actual database is getting close to the 256 column limit (we're at column HG now).

it's sort of go somewhere and bring something I do not know what. Be more specific or this thread will remain nearly empty.

Please see attachment.

Just a guess, but try this

In C2

=INDEX($E$10:$H$15,MATCH($A2,$E$10:$E$15,0),MATCH($B2,$E$10:$H$10,0))

Drag/Fill Down

Adjust to suit your actual ranges.

Hope this helps.

do you mean VB arrays, VB inputbox or some unknown for me method?

You don't have to use cell references for arguments - you can use constants if you prefer.

=MATCH("apple",A1:A10,0)

or

=MATCH(A1,{"apple";"banana";"carrot"},0)

the 2nd illustrates an inline array where ; denotes a row and , a column

=VLOOKUP(A1,{"apple",1;"banana",3;"carrot",4},2,0)

etc...

You cannot use D-functions without criteria ranges, if that's what you are referring to.

I'm afraid I don't follow.... if you are referring to the D-functions then see romperstomper's previous post (ie must be stored in physical range).

The clearer you are in your requirements the more likely you are to get an (satisfactory?) resolution... thus far everything has been far too vague for anyone to offer anything meaningful in return I'm afraid.

Per the initial sample file it appears as though you want to use constants for the criteria in your DGET call and as has been outlined previously you can't.

Edit: to elaborate on D functions
Given your set up in the original attachment you have an issue in so far as you can't create the requisite range reference (must be contiguous)
You could however use a basic SUMIF with INDEX construct

C2: 
=SUMIF($E$10:$E$15,$A2,INDEX($F$10:$H$15,0,MATCH($B2,$F$10:$H$10,0)))
copied down

but again based on response to Marcol's earlier suggestion this is not seemingly what you're interested in.