查找与一列中的键匹配的值,然后将其应用为键以从第三列返回值 [英] Lookup a value that matches a key in one column then apply that as a key to return a value from a third column
问题描述
我在G列和N列中的值具有相同的值但不是以相同的顺序,因此如果列G和N匹配,则将列L返回到列A.我从列L得到错误的值A栏。
I have values in columns G and N that have the same values, but not in the same order, so if column G and N matches, then return column L to column A. I am getting the wrong values from column L in column A.
除了下面的公式,还需要哪些其他信息?
What other information is needed besides formula below?
=IFERROR(VLOOKUP(N2,$G$2:$N$413,6,FALSE),"")
Col a Col b Col G Col L Col M Col N
ID CoID Items ItemsID ParentID Items1
45 1 Apple 45 1 Apple
推荐答案
因为你试图找到ColumnL中的ColumnL值找到ColumnN值的同一行我建议使用 INDEX MATCH 组合 - 类似于VLOOKUP但更强大(例如可以'向左看', VLOOKUP不能)。
Since you are trying to find the ColumnL value in the same row as you find the ColumnN value I’d suggest an INDEX MATCH combination – similar to VLOOKUP but more powerful (eg can ‘look to the left’, which VLOOKUP can’t).
=IFERROR(INDEX(L:L,MATCH(G2,N:N,0)),"")
MATCH查找位置,其中在ColumnN中找到G2,这就是行要返回的ColumnL值的编号。
MATCH looks for the position where G2 is found in ColumnN so that is the row number for the ColumnL value you want returned.
这篇关于查找与一列中的键匹配的值,然后将其应用为键以从第三列返回值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!