return value based on multiple strings

Hi!

I have real challange, and I want to do this without use of macro!! Me and my friend are making a training report. This is the report:

	A	B	C
1	NAME	TYPE	VALUE
2	Chris	Strength
3	Tom	Cardio	60
4	Chris	Strength	80
5	Tom	Strength	75

1.table

What we need is to return the FIRST value in column C, WHEN

• The FIRST value is above or below "0" (not empty cells)

• BOTH the strings "Tom" AND "Strength" matches. Like this:

	A	B	C
1	Chris	Strength	80
2	Tom	Strength	75

2. table

In other words: When A2 (in 2. table) matches A:A (in 1. table) AND B2 (in 2. table) matches B:B (in 1. table); return the value from C:C (1. table) to C2 (2. table). If the returned value is an empty cell, return the next value (based on the criterias mentioned).

I appreciate all help!

Bok1.xlsx

In C10
=INDEX($C$2:$C$5,MATCH(1,INDEX(($A$2:$A$5=A10)*($B$2:$B$5=B10)*($C$2:$C$5<>""),0),0))

Copy down

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.

http://www.excelforum.com/excel-form...e-strings.html