CODEWITHSUNDEEP
excelexcel-formulavlookup
TEAM
TEAM

Reputation: 55

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 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, [email protected], yes, no
Bob A, 1235, 123-111-1234, [email protected]. yes, no
Rachel C, 1236, 123-123-1234, [email protected], no, yes
Tim F, 1237, 123-123-2345, [email protected], 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.

Upvotes: 2

Views: 60

Answers (1)

VBasic2008
VBasic2008

Reputation: 54853

Multi-Lookup Using MAKEARRAY With INDEX and SEARCH

enter image description here

=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.

Upvotes: 1

Related Questions