字符串之间/之后的查找或索引匹配列表 [英] Lookup or index-match list between/after string

问题描述

我有一个自动生成的信息列表，可以给我3种不同动物的名称和体重. "Cat"，"Dog"和"Whale".我需要一个vlookup或index-match公式，以便在给定名称和动物类型的情况下向我返回相应的权重，如果不匹配则返回错误.

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.

输入:

     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

输出:

Animal  Cat
Name    Tommy
Weight  2.5

Animal  Dog
Name    Tommy
Weight  20

Animal  Whale
Name    Bob
Weight  Error

推荐答案

按照以下图片布局结果，在G6中使用此标准公式，然后复制到G10和G14.

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")

这篇关于字符串之间/之后的查找或索引匹配列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！