Excel VLookup返回不正确的查找结果问题

huangapple go评论72阅读模式
英文:

Excel VLookup issue with returning incorrect lookup results

问题

I am running into some issues with my two vlookup formulas, which are searching for words in a second sheet, then returning a yes or no into the columns.

On sheet 1 there are names and demographic info, then on sheet 2, there is demographic info, and also lists whether or not the person has signed two different forms

So here are the two formulas that I am using:

=IFERROR(IF(SEARCH("team",VLOOKUP($A2,Forms!$A$2:$B$300,2,FALSE))>0,"yes","no"),"no")
=IFERROR(IF(SEARCH("honey",VLOOKUP($A2,Forms!$A$2:$B$300,2,FALSE))>0,"yes","no"),"no")

So my problem that I am having is that for the "team" lookup formula, it's returning a 'yes' for most records, but there are some records that it's returning a 'no' when it should be 'yes', and for the "honey" formula, it's doing the opposite, it's returning mainly 'no' when they should be 'yes' and only returning 'yes' a couple of times.

Here is what each sheet looks like:

sheet 1

Name, member number, phone, email, team form, honey form
Adam B, 1234, 123-111-1231, a@aol.com, yes, no
Bob A, 1235, 123-111-1234, b@aol.com. yes, no
Rachel C, 1236, 123-123-1234, r@aol.com, no, yes
Tim F, 1237, 123-123-2345, t@aol.com, no, no
...

Sheet 2 (Forms)

Name, signed forms
Adam B, Uploaded TEAM Liability Form
Adam B, Uploaded Honey Run Liability Form
Bob A, Uploaded TEAM Liability Form
Bob A, Uploaded Honey Run Form
Rachel C, Uploaded TEAM Liability Form
Rachel C, Uploaded Honey Run Liability Form
Tim F, Uploaded Honey Run Liability Form
...

So as you can see from the sample of the data in each sheet, the formula should be matching that text all of the time, but I am not sure why it's not, and need some assistance with this.

I have also tried changing which column it's trying to match for instance instead of name, I tried matching by 'member number' so I would add that column to the Forms sheet, but no change. I tried changing the search text in formula 2 from 'honey' to 'liability' and then the formula worked perfectly, but not sure why the 'team' and 'honey' words are causing the formula not to work correctly all the time.

Any help would be very much appreciated.

英文:

I am running into some issues with my two vlookup formulas, which are searching for words in a second sheet, then returning a yes or no into the columns.

On sheet 1 there are names and demographic info, then on sheet 2, there is demographic info, and also lists whether or not the person has signed two different forms

So here are the two formulas that I am using:

=IFERROR(IF(SEARCH("team",VLOOKUP($A2,Forms!$A$2:$B$300,2,FALSE))>0,"yes","no"),"no")
=IFERROR(IF(SEARCH("honey",VLOOKUP($A2,Forms!$A$2:$B$300,2,FALSE))>0,"yes","no"),"no")

So my problem that I am having is that for the "team" lookup formula, it's returning a 'yes' for most records, but there are some records that its returning a 'no' when it should be 'yes', and for the "honey" formula, its doing the opposite, its returning mainly 'no' when they should be 'yes' and only returning 'yes' a couple of times.

Here is what each sheet looks like:

sheet 1

Name, member number, phone, email, team form, honey form
Adam B, 1234, 123-111-1231, a@aol.com, yes, no
Bob A, 1235, 123-111-1234, b@aol.com. yes, no
Rachel C, 1236, 123-123-1234, r@aol.com, no, yes
Tim F, 1237, 123-123-2345, t@aol.com, no, no
...

Sheet 2 (Forms)

Name, signed forms
Adam B, Uploaded TEAM Liability Form
Adam B, Uploaded Honey Run Liability Form
Bob A, Uploaded TEAM Liability Form
Bob A, Uploaded Honey Run Form
Rachel C, Uploaded TEAM Liability Form
Rachel C, Uploaded Honey Run Liability Form
Tim F, Uploaded Honey Run Liability Form
...

So as you can see from the sample of the data in each sheet, the formula should be matching that text all of the time, but I am not sure why its not, and need some assistance with this.

I have also tried changing which column its trying to match for instance instead of name, i tried matching by 'member number' so I would add that column to the Forms sheet, but no change. I tried changing the search text in formula 2 from 'honey' to 'liability' and then the formula worked perfectly, but not sure why the 'team' and 'honey' words are causing the formula not to work correctly all the time.

Any help would be very much appreciated.

答案1

得分: 1

  • 使用MAKEARRAYINDEXSEARCH进行多重查找
=LET(sn,Forms!A2:A8,sf,Forms!B2:B8,dn,A2:A5,
    t,"Team",h,"Honey",y,"Yes",n,"No",
MAKEARRAY(ROWS(dn),2,LAMBDA(r,c,
    IF(SUM((sn=INDEX(dn,r))*(ISNUMBER(
        SEARCH("*"&IF(c=1,t,h)&"*",sf)))))>0,y,n))))
  • 如果您没有Microsoft 365,可以尝试在单元格E2中使用以下公式:
=IF(SUMPRODUCT((Forms!$A$2:$A$8=$A2)*(ISNUMBER(
    SEARCH("*"&CHOOSE(MOD(COLUMN(E$1),2)+1,"Honey","Team")
        &"*",Forms!$B$2:$B$8)))))>0,"Yes","No")

复制到其他单元格。

  • 请注意,CHOOSE的结果取决于第一列,即列E,是否为奇数列。
英文:

Excel VLookup返回不正确的查找结果问题

=LET(sn,Forms!A2:A8,sf,Forms!B2:B8,dn,A2:A5,
    t,"Team",h,"Honey",y,"Yes",n,"No",
MAKEARRAY(ROWS(dn),2,LAMBDA(r,c,
    IF(SUM((sn=INDEX(dn,r))*(ISNUMBER(
        SEARCH("*"&IF(c=1,t,h)&"*",sf))))>0,y,n))))
  • If you don't have Microsoft 365, the following formula in cell E2 might work:
=IF(SUMPRODUCT((Forms!$A$2:$A$8=$A2)*(ISNUMBER(
    SEARCH("*"&CHOOSE(MOD(COLUMN(E$1),2)+1,"Honey","Team")
        &"*",Forms!$B$2:$B$8))))>0,"Yes","No")

Copy across.

  • Note that the result of CHOOSE depends on the 1st column, column E, being an odd column.

huangapple
  • 本文由 发表于 2023年6月4日 22:53:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/76400985.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定