Match and Index Problems

S

sourceminer

New Member
Joined
May 16, 2012
Messages
5
Hi MrExcel,

I have a problem with an Index and Match if Statement.
Here is the problem I am trying to solve.

HR has provided me a list of everyones First Name (B) and Last Name (C), Not everyone has an account on the network.
So I have a separate list of First Name (A), Last Name (B) (with a few other columns (7 to be exact)).
I need to update a cell that states "Has Account" =1 (Yes) =0 (No)

Here is my Formula:

=IF(INDEX('AD Accounts'!$A$2:$G$172,MATCH(1,('AD Accounts'!$A:$A=Data!B10)*('AD Accounts'!$B:$B=Data!C10),0),7),1,0)

AD Accounts Tab
[TABLE="width: 375"]
<tbody>[TR]
[TD="class: xl63, width: 87"]First Name[/TD]
[TD="class: xl63, width: 119"]Last Name[/TD]
[TD="class: xl63, width: 169"]Display Name[/TD]
[/TR]
[TR]
[TD="class: xl63"]Ronald[/TD]
[TD="class: xl63"]Udell[/TD]
[TD="class: xl63"]Ronald Udell[/TD]
[/TR]
[TR]
[TD="class: xl63"]David[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]David Wood[/TD]
[/TR]
[TR]
[TD="class: xl63"]Dev[/TD]
[TD="class: xl63"]Smith[/TD]
[TD="class: xl63"]Dev Smith[/TD]
[/TR]
[TR]
[TD="class: xl63"]Fran[/TD]
[TD="class: xl63"]Cheng[/TD]
[TD="class: xl63"]Fran Cheng[/TD]
[/TR]
</tbody>[/TABLE]

Data Tab
[TABLE="width: 458"]
<tbody>[TR]
[TD="class: xl63, width: 79"]Birth Date[/TD]
[TD="class: xl63, width: 99"]First Name[/TD]
[TD="class: xl63, width: 140"]Last Name[/TD]
[TD="class: xl63, width: 140"]Is in AD[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl65"]Everardo[/TD]
[TD="class: xl65"]Flores Velazquez[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl65"]Pedro[/TD]
[TD="class: xl65"]Morales[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl65"]Reynaldo[/TD]
[TD="class: xl65"]Mendez[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl65"]Ronald[/TD]
[TD="class: xl65"]Udell[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes

Excel 2010
ABC
1First NameLast NameDisplay Name
2RonaldUdellRonald Udell
3DavidWoodDavid Wood
4DevSmithDev Smith
5FranChengFran Cheng
AD Accounts



Excel 2010
ABCD
1Birth DateFirst NameLast NameIs in AD
2EverardoFlores Velazquez0
3PedroMorales0
4ReynaldoMendez0
5RonaldUdell1
Data Tab
Cell Formulas
RangeFormula
D2{=IF(ISERROR(MATCH(B2&C2,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}
D3{=IF(ISERROR(MATCH(B3&C3,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}
D4{=IF(ISERROR(MATCH(B4&C4,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}
D5{=IF(ISERROR(MATCH(B5&C5,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Hi!

Try this too:

=IF(COUNTIF('AD Accounts'!A:A,Data!B2)*COUNTIF('AD Accounts'!B:B,Data!C2),1,0)

Markmzz
 
Upvote 0
What a great outside the box solution.. Trying to think of any downside, and dont see any logical issues.
Than you all for the quick responses!

markmzz said:
Hi!

Try this too:

=IF(COUNTIF('AD Accounts'!A:A,Data!B2)*COUNTIF('AD Accounts'!B:B,Data!C2),1,0)

Markmzz
Click to expand...
 
Upvote 0
Tried this but it wasn't searching the entire contents of the index, I think perhaps because it was assuming that the same row will contain the same contents in the other table?

Thanks for the fast response.

Scott T said:
Excel 2010
ABC
First NameLast NameDisplay Name
RonaldUdellRonald Udell
DavidWoodDavid Wood
DevSmithDev Smith
FranChengFran Cheng

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>
AD Accounts ​



Excel 2010
ABCD
Birth DateFirst NameLast NameIs in AD
EverardoFlores Velazquez
PedroMorales
ReynaldoMendez
RonaldUdell

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

</tbody>
Data Tab​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=IF(ISERROR(MATCH(B2&C2,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]{=IF(ISERROR(MATCH(B3&C3,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]{=IF(ISERROR(MATCH(B4&C4,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5[/TH]
[TD="align: left"]{=IF(ISERROR(MATCH(B5&C5,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
sourceminer said:
What a great outside the box solution.. Trying to think of any downside, and dont see any logical issues.
Than you all for the quick responses!
Click to expand...
The problem is the is this.


Excel 2010
ABCDEFG
1Birth DateFirst NameLast NameIs in ADcountifs
2EverardoFlores Velazquez00
3PedroMorales00
4RonaldUdell11
5davidudell01Returns as 1 since the name david is in A of the other sheet and Udell is in B of the other sheet even though there is not a David Udell on the list
Data Tab
Cell Formulas
RangeFormula
F2=IF(COUNTIF('AD Accounts '!A:A,'Data Tab'!B2)*COUNTIF('AD Accounts '!B:B,'Data Tab'!C2),1,0)
F3=IF(COUNTIF('AD Accounts '!A:A,'Data Tab'!B3)*COUNTIF('AD Accounts '!B:B,'Data Tab'!C3),1,0)
F4=IF(COUNTIF('AD Accounts '!A:A,'Data Tab'!B4)*COUNTIF('AD Accounts '!B:B,'Data Tab'!C4),1,0)
F5=IF(COUNTIF('AD Accounts '!A:A,'Data Tab'!B5)*COUNTIF('AD Accounts '!B:B,'Data Tab'!C5),1,0)
D2{=IF(ISERROR(MATCH(B2&C2,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}
D3{=IF(ISERROR(MATCH(B3&C3,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}
D4{=IF(ISERROR(MATCH(B4&C4,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}
D5{=IF(ISERROR(MATCH(B5&C5,'AD Accounts '!$A$2:$A$5&'AD Accounts '!$B$2:$B$5,0)),0,1)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
sourceminer said:
Tried this but it wasn't searching the entire contents of the index, I think perhaps because it was assuming that the same row will contain the same contents in the other table?

Thanks for the fast response.
Click to expand...

How did it not work? Did you use CTL+SHIFT+ ENTER?
 
Upvote 0
markmzz said:
Hi!

Try this too:

=IF(COUNTIF('AD Accounts'!A:A,Data!B2)*COUNTIF('AD Accounts'!B:B,Data!C2),1,0)

Markmzz
Click to expand...

sourceminer said:
OK Mark thought of an issue, what if the last name is 2 names IE: Flores Velazquez, or Gonzalez-Ruelas
Click to expand...
Hi!

Sorry about that. Try this:

=IF(COUNTIFS('AD Accounts'!A:A,Data!B2,'AD Accounts'!B:B,Data!C2),1,0)

Or

=COUNTIFS('AD Accounts'!A:A,Data!B2,'AD Accounts'!B:B,Data!C2)
​

[TABLE="class: grid, width: 933"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Birth Date[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Is in AD[/TD]
[TD]Data[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Display Name[/TD]
[TD]AD Accounts[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Everardo[/TD]
[TD]Flores Velazquez[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Ronald[/TD]
[TD]Udell[/TD]
[TD]Ronald Udell[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Pedro[/TD]
[TD]Wood[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]David[/TD]
[TD]Wood[/TD]
[TD]David Wood[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Reynaldo[/TD]
[TD]Mendez[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Ronald[/TD]
[TD]Smith[/TD]
[TD]Ronald Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]
[/TD]
[TD]Ronald[/TD]
[TD]Udell[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Fran[/TD]
[TD]Cheng[/TD]
[TD]Fran Cheng[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Reynaldo[/TD]
[TD]Udell[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Reynaldo[/TD]
[TD]Flores Velazquez[/TD]
[TD]Reynaldo Flores Velazquez[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Ronald[/TD]
[TD]Smith[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Ronald[/TD]
[TD]Gonzalez-Ruelas[/TD]
[TD]Ronald Gonzalez-Ruelas[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*********[/TD]
[TD]**********[/TD]
[TD]****************[/TD]
[TD]********[/TD]
[TD]*****[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]**********[/TD]
[TD]*****************[/TD]
[TD]*************************[/TD]
[TD]************[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz
 
Last edited:
Upvote 0
Hi!

Try this another formula too:

=--(MAX(MMULT(--('AD Accounts'!A:B=Data!B2:C2),{1;1}))=2)

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
405
Alex Blakenburg
A
I
  • Question Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
569
StephenCrump
S
J
  • Question Question
Index Match and Arrays
Replies
1
Views
564
Herakles
H
N
  • Question Question
Index Match after sorting
Replies
1
Views
648
Eric W
Eric W
U
  • Solved
Index formula with matching - Can it look at rows and columns?
Replies
2
Views
388
usnapoleon
U

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top