英文:
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
-
使用 MAKEARRAY
、INDEX
和SEARCH
进行多重查找
=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
,是否为奇数列。
英文:
Multi-Lookup Using MAKEARRAY
With INDEX
and SEARCH
=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, columnE
, being an odd column.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论