VLOOKUP / INDEX Problem

M

MrRC

New Member
Joined
Jun 16, 2011
Messages
18
Dear sirs,

I'm having some problems with an excel formula and i would like to hear your expert opinions on this :)
I'm using Excel 2007 and I have 2 Sheets:
Sheet1:
___A B C D E F
bla X X
ble X

Sheet2:
Name Letter
bla__ A
ble__ A
bla__ B

How can i make a formula to populate Sheet1 with an X the letters that it finds on the contact auto?

I'm using =IF(INDEX(Contactos!$CI2:$CI$2445;SMALL(IF(Contactos!$CE2=Contactos!$CE2:$CE$2445;ROW(Contactos!$CE2:$CE$2445)-MIN(ROW(Contactos!$CE2:$CE$2445))+1; ""); ROW($A$1)))=AP$1;"X";"") it works, but only finds the first item.. i'm going the wrong way

Thanks for your help :)
 
Sort by date Sort by votes
MrRC said:
It's working :)<TABLE cellSpacing=0 cellPadding=0 width=64 border=0><TBODY><TR height=20><TD style="WIDTH: 48pt; HEIGHT: 15pt" width=64 height=20>

</TD></TR></TBODY></TABLE>=IF(SUMPRODUCT((Contactos!$CE$2:$CE$2445=Contactos!$CE65)*(Contactos!$CI$2:$CJ$2445=AL$1));"X";"")

However if i want to add a third "check" with this formula how can I do it ?

With Countif i just added a new criteria..

=IF(COUNTIFS(Contactos!$CE$2:$CE$2445;Contactos!$CE62;Contactos!$CG$2:$CG$2445;Contactos!$CG62;Contactos!$CI$2:$CI$2445;AM$1);"X";"")

Thanks for all the help!

<TABLE cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL width=64><TBODY><TR height=20><TD style="WIDTH: 48pt; HEIGHT: 15pt" width=64 height=20>

</TD></TR></TBODY></TABLE>
Click to expand...
What's the third "check"?

How many more "checks" are you going to add? We started with 1 and now we're up to 3!
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sorry,

=IF(COUNTIFS(Contactos!$CE$2:$CE$2445;Contactos!$CE62;Contactos!$CG$2:$CG$2445;Contactos!$CG62;Contactos!$CI$2:$CI$2445; AM$1);"X";"")

1st Criteria - Checks Name
2nd Criteria - Checks Partner Name
3rd Criteira - Checks the Course Name
 
Upvote 0
MrRC said:
Sorry,

=IF(COUNTIFS(Contactos!$CE$2:$CE$2445;Contactos!$CE62;Contactos!$CG$2:$CG$2445;Contactos!$CG62;Contactos!$CI$2:$CI$2445; AM$1);"X";"")

1st Criteria - Checks Name
2nd Criteria - Checks Partner Name
3rd Criteira - Checks the Course Name
Click to expand...
I'm not following you on this.

That formula has 3 conditions:
  1. Contactos!$CE$2:$CE$2445 = Contactos!$CE62
  2. Contactos!$CG$2:$CG$2445 = Contactos!$CG62
  3. Contactos!$CI$2:$CI$2445 = AM$1
:confused:
 
Upvote 0
Yes,

  1. Contactos!$CE$2:$CE$2445 = Contactos!$CE62
  2. Contactos!$CG$2:$CG$2445 = Contactos!$CG62
  3. Contactos!$CI$2:$CI$2445 = AM$1
and i would like to to use this one too

4. Contactos!$CJ$2:$CJ$2445 = AM$1
 
Upvote 0
MrRC said:
Yes,

  1. Contactos!$CE$2:$CE$2445 = Contactos!$CE62
  2. Contactos!$CG$2:$CG$2445 = Contactos!$CG62
  3. Contactos!$CI$2:$CI$2445 = AM$1
and i would like to to use this one too

4. Contactos!$CJ$2:$CJ$2445 = AM$1
Click to expand...
OK, did you add it to the formula? And did it not work?

Are conditions 3 and 4 supposed to be an "OR" condition?

CI2:CI2445 = AM1 OR CJ2:CJ2445 = AM1
 
Upvote 0
Not OR, because 3. always have a value and 4. only sometimes..

With COUNTIFS or SUMPRODUCT i don't know how to add this..
 
Upvote 0
MrRC said:
Not OR, because 3. always have a value and 4. only sometimes..

With COUNTIFS or SUMPRODUCT i don't know how to add this..
Click to expand...
With COUNTIFS:

=IF(COUNTIFS(Contactos!$CE$2:$CE$2445;Contactos!$CE62;Contactos!$CG$2:$CG$2445;Contactos!$CG62;Contactos!$CI$2:$CI$2445; AM$1;Contactos!$CJ$2:$CJ$2445;AM$1);"X";"")
 
Upvote 0
This doesn't work because, because it assumes that Condition 3 and Condition 4 always have some value.. where is not the case

I'm sorry but I'm very mixed about this.
 
Upvote 0
MrRC said:
This doesn't work because, because it assumes that Condition 3 and Condition 4 always have some value.. where is not the case

I'm sorry but I'm very mixed about this.
Click to expand...
Can you post some sample data so I can see what you're wanting to do?
 
Upvote 0
Ok here's an example

Contactos Sheet
<table border="0" cellpadding="0" cellspacing="0" width="876"><col style="mso-width-source:userset;mso-width-alt:7424; width:152pt" span="2" width="203"> <col style="mso-width-source:userset;mso-width-alt:10166;width:209pt" width="278"> <col style="width:48pt" span="3" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:152pt" width="203" height="20">Nome Completo</td> <td style="width:209pt" width="278">Partner</td> <td style="width:48pt" width="64">Interest 1</td> <td style="width:48pt" width="64">Interest 2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl117" style="height:15.0pt" height="20">Name1</td> <td class="xl117">P1</td> <td class="xl117">CRM</td> <td class="xl117" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl117" style="height:15.0pt" height="20">Name2</td> <td class="xl117">P2
</td> <td class="xl117">SOA</td> <td class="xl117" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl117" style="height:15.0pt" height="20">Name3</td> <td class="xl117">P2
</td> <td class="xl117">CRM</td> <td class="xl117" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl117" style="height:15.0pt" height="20">Name4</td> <td class="xl117">P3
</td> <td class="xl117">CRM</td> <td class="xl117" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl117" style="height:15.0pt" height="20">Name5</td> <td class="xl117">P4
</td> <td class="xl117">ERP</td> <td class="xl117" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl117" style="height:15.0pt" height="20">Name6</td> <td class="xl117">P5
</td> <td class="xl117">ERP</td> <td class="xl117" align="right">HCM
</td> </tr> </tbody></table>
This is the main

<table border="0" cellpadding="0" cellspacing="0" width="1377"><col style="width:48pt" span="12" width="64"> <col style="mso-width-source:userset;mso-width-alt:3547;width:73pt" width="97"> <col style="width:48pt" span="8" width="64"> <tbody><tr style="mso-height-source:userset;height:49.5pt" height="66"> <td class="xl117" style="height:49.5pt;width:48pt" width="64" height="66">
</td> <td class="xl123" style="width:48pt" width="64">Perf. & Avail.</td> <td class="xl123" style="border-left:none;width:48pt" width="64">Storage & DW</td> <td class="xl123" style="border-left:none;width:48pt" width="64">Security</td> <td class="xl124" style="border-left:none;width:48pt" width="64">SAM</td> <td class="xl125" style="width:48pt" width="64">SOA</td> <td class="xl125" style="border-left:none;width:48pt" width="64">E2.0</td> <td class="xl125" style="border-left:none;width:48pt" width="64">BI</td> <td class="xl126" style="border-left:none;width:48pt" width="64">IDM</td> <td class="xl127" style="width:73pt" width="97">ERP</td> <td class="xl127" style="border-left:none;width:48pt" width="64">EPM</td> <td class="xl127" style="border-left:none;width:48pt" width="64">CRM</td> <td class="xl127" style="border-left:none;width:48pt" width="64">CRM OD</td> <td class="xl127" style="border-left:none;width:48pt" width="64">HCM</td> <td class="xl127" style="border-left:none;width:48pt" width="64">BI Apps</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Name1
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>X</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Name2</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>X
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Name3</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>X</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Name4</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>X</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Name5</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>X
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Name6</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>X
</td> <td>
</td> <td>
</td> <td>
</td> <td>X
</td> <td>
</td> </tr> </tbody></table>
It should check Name and Partner (because sometimes the names are repeated in differente partners) then it should check for interest 1 and 2..

=IF(COUNTIFS(Contactos!$CE$2:$CE$2445;CE2;Contactos!$CG$2:$CG$2445;$C4;Contactos!$CI$2:$CI$2445; AP$1);"X";"")

Does this very, but only checks Interest 1..

Thanks man
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Selective Match/Vlookup (?) on Various Criteria in Single Row
Replies
2
Views
280
m3ko
M
N
  • Solved
Problem populating table based off two criteria from a different table
Replies
1
Views
880
Nade85
N
B
  • Question Question
Sheetname 'problem' and another Index-match problem
Replies
2
Views
657
Blackov
B
A
  • Question Question
Best function for current problem
Replies
1
Views
509
SanjayGMusafir
SanjayGMusafir
R
  • Solved
VLOOKUP or INDEX/MATCH Help (Possible VBA)
Replies
3
Views
507
Saba Sabaratnam
S

Forum statistics

Threads
1,225,156
Messages
6,183,242
Members
453,152
Latest member
ChrisMd

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