plampe
asked on
Excel DGET Function with multiple criteria
I am trying to use the dget function where the row selected matches two criteria. The original formula was
DGET(BI8:CN33, "time",CN53:CN54), but I wanted to narrow down the selection to remove duplicates (since DGET will not work when there are duplicates) by using a second criteria CO53:CO54. I've tried using an AND statement, but can't seem to get the formula to come out or get the syntax right.
DGET(BI8:CN33, "time",CN53:CN54), but I wanted to narrow down the selection to remove duplicates (since DGET will not work when there are duplicates) by using a second criteria CO53:CO54. I've tried using an AND statement, but can't seem to get the formula to come out or get the syntax right.
plampe, what type of criteria ?
Are you trying to return a value or a string ? If returning a value you could use a different sumproduct function.
ASKER
B9:CO33 contain values and row 8 contains a list of field names (B8=Time, CN8=Gross, CO8=Net). CN53 contains the field name Gross and CN54 contains the maximum value from CN9:CN33. CO53 contains the field name Net and CO54 contains the maximum value from CO9:CO33. I am trying to select the Time where the Gross is equal to the maximum value and the Net is also equal to the maximum value. This should always occur at the same time.
But if using another formula than DGET, that does not give error values for duplicates, you only need one criteria since the maximum values occur at the same time...right?
If yes, try this one:
=INDEX(B9:B33;MATCH(MAX(CN 9:CN33);CN 9:CN33;0))
If no..well then forget about the above.
Matthias
If yes, try this one:
=INDEX(B9:B33;MATCH(MAX(CN
If no..well then forget about the above.
Matthias
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
just a precautionary note... make sure you select the single cells with the values for the criteria
oh, and in my example, i only went to row 18... You will want to replace the 18's with 33's
Can you please describe what is in these columns so I can understand better?
Sounds like you have data in B18:CN33 where row 18 is a list of field names. One of the fields is called time.
You want to extract the time value from these rows that fall between the values in CN53:CN54??