Vlookup with multiple criteria

Question

Thanks for your help last time with the problem I had, it worked perfectly,  :-) I've got a new report now, and a new problem to go with it.

I am trying to do a VLOOKUP that will look for 2 different 'lookup_values' within the same 'table_array' to bring back 1 'col_index_num'. I know that vlookup probably won't work, but I've tried INDIRECT, MAX & IF's e.t.c. But I just can't get my head around it,  :-) Hope that makes sense.

All of the look up values will always be "Text", but the value I need to bring back from the 8th column (if it was a VLOOKUP) will always be [hh]:mm:ss, if that makes a difference,

I'm using2003.

Thanks in advance, Peter.

Answer

Peter,

If you are bringing back a numbers (a time value is a number) and the combination of the two values will identify a single row, then you can sue sumproduct

assume you want to find the row with "Value1" in column B and "Value2" in column F and return the time value from column H then you could do:

=sumproduct(--(B2:B500="Value1"),--(F2:F500="Value2"),H2:H500)

format the cell with the formula as Time ([hh]:mm:ss)

Is that what you want?