Vlookup & IF formula - HELP!

[uptonj]

Hi, trying to work a formula to look up a value in a colum that has multiple entries of the vale based on the value of another column.

My value is in cell A2 (value = 1234567). The value is shown in Sheet1 in column A along with responding data in columns B & C. My value however is duplicated in column A with different responding data in columns B & C. Example below:


Cell A2 = 1234567
Cell B2 = Completed
Cell C2 = 12:52:34

Cell A3 = 1234567
Cell B3 = Started
Cell C3= 12:43:49

So I want to lookup my value if the value = 1234567 & if the value in column B = Completed to bring back the value (in this case the time) in column C.

IS THIS EVEN POSSIBLE?!?!

Regards,

Jake

 

[dispelthemyth]

Try something like the below

$G$1:$G$6 is the range you want to bring a value back from
$A2&B$1 is the value you want to look up (1234567Completed)
$E$1:$E$6&$F$1:$F$6 is the new match range, combining Column E & F


Sheet1

*	A	B	C	D	E	F	G
1		Completed
2	1234567	12:43:49			1234567	Started	12:52:34
3					1234567	Completed	12:43:49
4
5
6

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:76px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:67px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 {=INDEX($G$1:$G$6, MATCH($A2&B$1, $E$1:$E$6&$F$1:$F$6,0),1)} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[hippiehacker]

=INDEX(C1:C20;MATCH(1;(A1:A20=1234567)*(B1:B20="Completed");0))


Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

 

[jim may]

Here's a 3rd method.

Excel 2010
	A	B	C	D	E	F
1					Lookup>>	1234567
2	1234567	Complete	12:52:34		Lookup>>	Complete
3	1234567	Started	12:43:49		Answer Is:	12:52:34
Sheet1

Cell Formulas
Range	Formula
F3	=SUMPRODUCT(--(A2:A6=F1),--(B2:B6=F2),C2:C6)

 

[uptonj]

Works like a treat. Thank you so much!

 

[uptonj]

You are all brilliant, working through this now at leopard speed!

Rally appreciated

Jake