index and match, lookup two values and return adjacent cell

[martin1230]

i have a project larger than the table below where I need to get the value of total every time I lookup the name in yellow. I have =INDEX(A1:C10,MATCH(A1,A1:A10,0)+5,3).

now the problem is I need to be able to extract this data from another workbook and still be able to get the adjacent cell from total. As you can see the number of rows differ from my name and george's name. so the first lookup will be "martin" then lookup the "total" under and give me the adjacent number.

can anyone help me

[#2]

@martin1230 

Say the name that you want to look up is in E1, you could try:

=INDEX(INDEX(C1:C10,MATCH(E1,A1:A10,0)):C10,MATCH("total",INDEX(A1:A10,MATCH(E1,A1:A10,0)):A10,0))

[#3]

Thank you, this solved the problem.