try this

=INDEX([excel1]Sheet1!C1:C5,MATCH(A2,[excel1]Sheet1!B1:B5,0),0)



On Oct 9, 5:13*pm, IJALAB wrote:
Hello All,

Thanks for your guidance.
I have two spreadsheets. One spreadsheet(excel 1) has two columns and
whose values aresample)
testid|require# |Result
a1.1 |10.01.01 |Pass
a1.2 |10.01.02 |Nottest
a1.3 |10.01.01 |Pass
a1.4 |10.01.01 |Pass
a1.5 |10.01.03 |Fail

Now that a1.1,a1.3,a1.4 are all a part of 10.01.01 and in another
spreadsheet(excel 2), i will have only the require#.
require# |Result
10.01.01 |<result
10.01.02 |<result
10.01.03 |<result ....so on

My need is to update (excel 2)'s result column based on results from
excel 1
In my case, only if a1.1,a1.3,a1.4 are passed, 10.01.01 should be made
PASSED otherwise it should be always NOTTESTED.

Please help me in the first part atleast where i will do a database
lookup for all the test cases that are mapped to a particular
requirement# and look for the test status for the mapped test case
ids.

thanks
bala