Lookup or index-match list between/after string

问题:

I have an auto-generated list of information that gives me the name and weight of 3 different animals. "Cat" , "Dog" and "Whale". I need a vlookup or index-match formula to return to me the respective weight if given name and animal type and return error if it doesn't match.

I've found the solution for lookup between values (i.e. numbers) but can't seem to get it to work for string values.

Input:

     A       B
1    Cat     Weight
2    Bob     1.5
3    Tommy   2.5
4    Peter   3.5
5    Adam    4.5
6    Mary    5.5
7    Dog    
8    Bob     14
9    Adam    16
10   Peter   15
11   Tommy   20
12   Whale  
13   Peter   150
14   Adam    188
15   Jack    164

Output:

Animal  Cat
Name    Tommy
Weight  2.5

Animal  Dog
Name    Tommy
Weight  20

Animal  Whale
Name    Bob
Weight  Error

回答1:

With your results laid out as per the image below, use this standard formula in G6 and copy to G10 and G14.

=IFERROR(INDEX(B:B, MATCH(G5, INDEX(A:A, MATCH(G4, A:A, 0)):INDEX(A:A, AGGREGATE(15, 6, ROW($1:$999)/((ROW($1:$999)>MATCH(G4, A:A, 0))*NOT(LEN(B$1:B$999))), 1)), 0)+MATCH(G4, A:A, 0)-1), "error")