Get a value from a list (Database) aka DGet

  • Thread starter Thread starter Yakimo
  • Start date Start date
Y

Yakimo

Hi folks,

I have a flat table with 3 columns
Name Time Value
-------------------------
John 0:30 7
Peter 0:40 8
Joe 0:50 1
John 1:30 88

Can I use somehow a function in order to get f.ex. the value for John,
i.e.
=F(Value,Peter,0:40)
DGet has similar functionality, but the criteria expression a column
containing a range

It seems I need something like GetPivotData, but I don't have a Pivot table.
Of course I can build one and get my values, but it seems not the right
solution
I have another solution also - to concatenate the first 2 columns and later
on to use SumIf, where the range will be the concatenated column and the
criteria will be concatenation of my 2 fields..

Is there any other more elegant solution?

Thanks,
Yakimoto
 
You can use SumProduct:

=SUMPRODUCT(($A$2:$A$5=$F2)*($B$2:$B$5=G$1)*($C$2:$C$5))

where F2=John and G1=0:30
 
thanks, Debra

that is what i needed
yakimoto

Debra Dalgleish said:
You can use SumProduct:

=SUMPRODUCT(($A$2:$A$5=$F2)*($B$2:$B$5=G$1)*($C$2:$C$5))

where F2=John and G1=0:30
Click to expand...
 
Debra, your solution is perfect for me, when I deal with numbers in Value
column.
Do you know how I can do the same when my value is not a number, but a
string?
Example
John 0:30 Food

=func("John","0:30")
I expect to return "Food"

Thanks,
Yakimo
 
Back
Top