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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
459
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
598
StephenCrump
S
J
  • Question Question
Index Match and Arrays
Replies
1
Views
568
Herakles
H
N
  • Question Question
Index Match after sorting
Replies
1
Views
653
Eric W
Eric W
U
  • Solved
Index formula with matching - Can it look at rows and columns?
Replies
2
Views
413
usnapoleon
U

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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