VLookup based on two criteria
 

I've read through many posts on this topic but I've not been able to
find a solution to my problem.

On one tab I have six columns of information (table_array.) Column
"A" contains a machine name, Column "B" contains a fault name and
column "F" contains a time value (decimal minutes.) For each machine
there may be any number of faults associated to it. I am trying to
return the time value for a specific machine's fault name to a
corresponding cell on another tab in the same worksheet.

The lookup_value will come from another reference in the document; I
can work through that but cannot resolve the two criteria requirement
(machine and fault name) to pull in the time value.

Any response is appreciated.

KG

VLookup based on two criteria
 

I was working on this same issue today and found several posts on the
SumProduct ( ) function and I have it working for me.

" wrote:

I've read through many posts on this topic but I've not been able to
find a solution to my problem.

On one tab I have six columns of information (table_array.) Column
"A" contains a machine name, Column "B" contains a fault name and
column "F" contains a time value (decimal minutes.) For each machine
there may be any number of faults associated to it. I am trying to
return the time value for a specific machine's fault name to a
corresponding cell on another tab in the same worksheet.

The lookup_value will come from another reference in the document; I
can work through that but cannot resolve the two criteria requirement
(machine and fault name) to pull in the time value.

Any response is appreciated.

KG

VLookup based on two criteria
 

This can be done in a couple of ways. Sumproduct will do it assuing there is
only one instance of a given fault for each machine. (if a machine has the
same typ eof faule occure more than once then it will return the sum of the
times which will make no sense) as has already been posted. It would look
something like this

=sumproduct(--(A2:A100 = "machine a"), --(B2:B100 = "Fault b"), F2:F100)

You could also do this as a vlookup but you will need to create a
concatenated key to work with. Insert a New column in front of Column A. Add
the Formula
= B2 & C2
in cell A2. copy it down...

Where you want to return the value
=Vlookup("machine aFaultb", A2:G100, 7, false)
--
HTH...

Jim Thomlinson


" wrote:

I've read through many posts on this topic but I've not been able to
find a solution to my problem.

On one tab I have six columns of information (table_array.) Column
"A" contains a machine name, Column "B" contains a fault name and
column "F" contains a time value (decimal minutes.) For each machine
there may be any number of faults associated to it. I am trying to
return the time value for a specific machine's fault name to a
corresponding cell on another tab in the same worksheet.

The lookup_value will come from another reference in the document; I
can work through that but cannot resolve the two criteria requirement
(machine and fault name) to pull in the time value.

Any response is appreciated.

KG